Controllare l'univocità dei dati

Ultima modifica: 29-05-2016

Abbiamo già avuto modo di vedere come poter controllare i nostri inserimenti controllando direttamente i tasti premuti o comunque prima dell'utilizzo ciò che è stato digitato in una TextBox o in una cella del foglio di Excel usandone gli opportuni eventi: KeyPress, BeforeUpdate (per una TextBox) o Change (per il foglio di lavoro).

Ora vorrei poter illustrare come controllare l'univocità dei dati che inseriamo. Infatti può succedere che dobbiamo aver a che fare con dati che dobbiamo usare per gli usi più disparati:

Sugli inserimenti effettuati in moduli, tabelle o altro dobbiamo poter avere la necessità e/o la possibilità di effettuare controlli o statistiche.

Provate ad immaginare se in un turno ci capita di scrivere Francesco, oppure Farncesco, oppure francesco; se in una gestione magazzino scriviamo Avvitatore oppure Avitatore.

Dovendo alla fine eseguire delle statistiche o qualsiasi altro studio dalle tabelle derivanti senz'altro verremmo incontro a serie difficoltà.

Da queste considerazioni nasce la necessità di istruire dei controlli appositamente studiati per impedire l'inserimento errato dei dati. In un applicativo fatto per il mio posto di lavoro, una volta ho provato ad adottare la convalida dati offerta da Excel tramite il comando: Dati / Convalida. Ma ho dovuto abbandonare il progetto in quanto mi rallentava il lavoro ed era scomodo da utilizzare.

A questo punto si potrebbe optare per varie soluzioni:

  1. attivare la convalida dati direttamente sul foglio di lavoro, già menzionato qui sopra, ed ho verificato che in molti casi è abbastanza scomodo
  2. effettuare l'inserimento tramite UserForm permanente anche questo in certi casi scomodo
  3. lasciare l'inserimento libero da tastiera ma controllarlo tramite codice e far intervenire una UserForm solo in caso di necessità
  4. richiamare una UserForm solo se si desidera farlo

In questo progetto ho optato per le ultime due soluzioni. In questo modo, se i dati inseriti manualmente in una colonna sono ripetuti, possiamo fruire anche di un ulteriore aiuto che Excel ci offre col suo completamento automatico che effettua quando digitiamo le prime lettere che corrispondono al contenuto di celle sovrastanti quella di inserimento.

 

Sul primo foglio di Excel abbiamo impostato una tabella simile a questa:

Mese di ottobre
PROSPETTO MENSILE VENDITE
           
Cod Data Caus Banco Magazzino Ufficio Cliente Merce Q.tà
                 
                 

Compito di questa tabella è quello di monitorare l'operato dei nostri collaboratori che svolgono il loro lavoro nei vari settori. Un gruppo di persone lavora al Banco, un gruppo al Magazzino, un altro in Ufficio.

Sempre su questo primo foglio disegniamo un pulsante prelevato da "Strumenti di Controllo" (ActiveX).

La routine che andremo a scrivere per questo pulsante farà in modo che, a seconda della colonna su cui ci troviamo, ci mostrerà una delle tre UserForm dalla cui casella combinata preleveremo i nomi da inserire nella cella attiva del foglio di lavoro.

 

Sul terzo foglio elencheremo i nomi di tutti gli operatori dei tre settori in una tabella del genere:

  A B C D E
1 Banco Magazzino Ufficio
2      
3      
4 Liberali Franca Dionisio Fiorenzo Berte' Carlo
5 Di Maggio Mario Traina Giovanni Benzi Mario
6 Scicchitano Andrea Orlandi Enrico Mira Mario
7 Maggi Massimo Odelli Giuseppe Ciani Orazio
8 Edelvisi Maurizio Poggi Claudio Rossi Claudio
9 Gerardini Silvano Ravizzola Stefano Boscati Andrea
10 Pasotti Alessio Bazzotti Domenico Bazzotti Pietro
11 Calvi Giovanni Quarti Romano Scicchitano Pierluigi
12 Poggi Giuseppe Pozzati Roberto  
13 Curti Oreste Palma Giovanni  
14 Borsotti Mirella Lagana' Ferruccio  
15 Franchinotti Anna Castoldi Francesco  
16 Pischedda Giovanni Scarani Christian  
17 Maini Marilena Campagna Maria  
18   Fracchia Genny  
19   Moncalvi Sandro  
20      
21      
22      

 

Abbiamo detto all'inizio che l'inserimento dei nomi relativi agli operatori dei diversi settori avverrà in uno dei seguenti modi:

  1. inserimento libero da tastiera ma controllarlo tramite codice e far intervenire una UserForm solo in caso di necessità
  2. inserimento tramite una UserForm solo se si desidera farlo o se si sbaglia a digitare il nome

 

Gestione delle UserForm

Andiamo nel VBA in uno dei modi che oramai sappiamo.

Come andare in VBA

  1. ALT + F11
  2. Strumenti --> Macro --> Visual Basic Editor
  3. clic sul pulsante Visual Basic Editor dalla barra degli strumenti Visual Basic

Come prima operazione creiamo tre UserForm come quelle mostrate qui sotto:

  1. una UserForm per la scelta dell'operatore al banco
  2. una UserForm per la scelta dell'operatore al magazzino
  3. una UserForm per la scelta dell'operatore all'ufficio

NB Ricordo che per creare una nuova UserForm occorre

  1. fare clic destro nella finestra Progetto VBAProject
  2. dal menù contestuale che ne deriva scegliamo Inserisci --> UserForm

 

Il codice per gestire le UserForm

In ciascuna di queste UserForm lavoriamo in questi eventi:

  1. CommandButton1_Click
  2. CommandButton2_Click
  3. UserForm_Activate

Questo è il codice completo per ciascuna di queste UserForm:

Banco Magazzino Ufficio

Private Sub CommandButton1_Click()
If ComboBox1.Text = "" Then
MsgBox "Occorre scegliere un nome o scegliere ANNULLA", vbCritical
Exit Sub
End If
Application.EnableEvents = False
ActiveCell = ComboBox1.Text
Application.EnableEvents = True
UserForm1.Hide
Unload UserForm1
End Sub


Private Sub CommandButton2_Click()
ActiveCell.ClearContents
UserForm1.Hide
Unload UserForm1
End Sub


Private Sub UserForm_Activate()
' banco
With Sheets("Foglio3")
R3 = 4
While .Cells(R3, 1) <> ""
R3 = R3 + 1
Wend
R3 = R3 - 1
For I = 4 To R3
ComboBox1.AddItem .Cells(I, 1)
Next
End With
End Sub

Private Sub CommandButton1_Click()
If ComboBox1.Text = "" Then
MsgBox "Occorre scegliere un nome o scegliere ANNULLA", vbCritical
Exit Sub
End If
Application.EnableEvents = False
ActiveCell = ComboBox1.Text
Application.EnableEvents = True
UserForm2.Hide
Unload UserForm2
End Sub


Private Sub CommandButton2_Click()
ActiveCell.ClearContents
UserForm2.Hide
Unload UserForm2
End Sub


Private Sub UserForm_Activate()
' magazzino
With Sheets("Foglio3")
R4 = 4
While .Cells(R4, 3) <> ""
R4 = R4 + 1
Wend
R4 = R4 - 1
For I = 4 To R4
ComboBox1.AddItem .Cells(I, 3)
Next
End With
End Sub

Private Sub CommandButton1_Click()
If ComboBox1.Text = "" Then
MsgBox "Occorre scegliere un nome o scegliere ANNULLA", vbCritical
Exit Sub
End If
Application.EnableEvents = False
ActiveCell = ComboBox1.Text
Application.EnableEvents = True
UserForm3.Hide
Unload UserForm3
End Sub


Private Sub CommandButton2_Click()
ActiveCell.ClearContents
UserForm3.Hide
Unload UserForm3
End Sub


Private Sub UserForm_Activate()
' ufficio
With Sheets("Foglio3")
R5 = 4
While .Cells(R5, 5) <> ""
R5 = R5 + 1
Wend
R5 = R5 - 1
For I = 4 To R5
ComboBox1.AddItem .Cells(I, 5)
Next
End With
End Sub

 

Preparate così le UserForm, possiamo partire per preparare le due diverse procedure:

  1. quella per inserire i dati manualmente
  2. quella per inserire i dati aiutati dalle UserForm

Inserimento dei dati manualmente

Per avere il controllo su quel che digitiamo sul foglio dobbiamo usare l'evento Worksheet_Change. Ma, per evitare spiacevoli verificarsi di errori ho deciso anche di impedire che sul foglio vengano effettuate selezioni di celle multiple. Quindi ho deciso di controllare anche questa eventualità. Questo lo controllo usando anche l'evento Worksheet_SelectionChange.

Ora, se non siamo già in VBA, ci torniamo.

Scegliamo il modulo relativo al foglio che dobbiamo monitorizzare, facendo doppio clic sul nome del foglio presente nella finestra Progetto VBAProject.

In questa finestra possiamo scrivere la routine per il controllo sugli inserimenti che si effettueranno nel foglio.

 

Iniziamo con l'impedire la selezione multipla di celle usando l'evento Worksheet_SelectionChange:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then ActiveCell.Select
End Sub

Fatto questo possiamo passare con lo scrivere la routine che controlla il nostro inserimento usando l'altro evento del foglio e cioè il Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Uriga, R2, C2, R1, R3, R
Dim Nome, I
If Target = "" Then Exit Sub ' se la cella modificata è vuota si esce dalla routine e non si fa altro
R2 = Target.Row
C2 = Target.Column ' si leggono le coordinate della cella modificata
' se ci troviamo nella colonna 4, 5 o 6 e la cella superiore è vuota, non si possono effettuare inserimenti
If (C2 = 4 Or C2 = 5 Or C2 = 6) And Target.Offset(-1, 0) = "" Then
MsgBox "Questa non è una posizione corretta"
Target = ""
Target.Select
Exit Sub
End If
Nome = Target ' si legge il dato appena inserito
With Sheets("Foglio3") ' si va a controllare se il nome inserito è presente nella relativa lista
R3 = 4
Select Case C2 ' il controllo si esegue in base alla colonna in cui è stato inserito il nome
Case 4
' banco
While .Cells(R3, 1) <> ""
If Nome = .Cells(R3, 1) Then Exit Sub
R3 = R3 + 1
Wend ' se si giunge alla fine del ciclo senza aver trovato una corrispondenza col nome cercato
Target.Select
UserForm1.Show ' viene aperta la relativa UserForm per inserire il nome prelevandolo dall'elenco
Case 5
' magazzino
While .Cells(R3, 1) <> ""
If Nome = .Cells(R3, 3) Then Exit Sub
R3 = R3 + 1
Wend ' stessa cosa per questa colonna
Target.Select
UserForm2.Show
Case 6
' ufficio
While .Cells(R3, 1) <> ""
If Nome = .Cells(R3, 5) Then Exit Sub
R3 = R3 + 1
Wend ' e per quest'altra
Target.Select
UserForm3.Show
End Select
End With
End Sub

Il codice è abbastanza commentato ed i commenti spiegano quel che succede nel codice appeva visto.

 

Inserimento dati tramite UserForm

In questo caso, per aprire una delle UserForm, usiamo un pulsante prelevato dalla barra degli strumenti Strumenti di controllo.

Se non stiamo in modalità progettazione facciamo clic sull'icona con riga, squadra e matita che si trova in questa barra degli strumenti

Fatto questo facciamo doppio clic sul pulsante activex per essere trasportati nel giusto modulo del VBA (il modulo relativo al foglio su cui stiamo lavorando).

In questo modulo troviamo anche la routine inizializzata:

Private Sub CommandButton1_Click()

 

End Sub

E' in questa rouitine che andremo a scrivere il codice per aprire la giusta UserForm.

NB: Ricordo ancora che abbiamo a disposizione tre UserForm che verranno aperte a seconda della colonna in cui ci troviamo:

  1. se ci troviamo nella colonna 4 (D) verrà aperta la UserForm1
  2. se ci troviamo nella colonna 5 (E) verrà aperta la UserForm2
  3. se ci troviamo nella colonna 6 (F) verrà aperta la UserForm3
  4. se non ci troviamo in una di queste colonne, non verrà aperta alcuna UserForm.

 

Private Sub CommandButton1_Click()
Dim R2, C2
R2 = ActiveCell.Row
C2 = ActiveCell.Column
If (C2 = 4 Or C2 = 5 Or C2 = 6) And ActiveCell.Offset(-1, 0) = "" Then
MsgBox "Questa non è una posizione corretta"
ActiveCell.Activate
Exit Sub
End If
If C2 <> 4 And C2 <> 5 And C2 <> 6 Then
ActiveCell.Activate
Exit Sub
End If
Select Case C2
Case 4
UserForm1.Show
Case 5
UserForm2.Show
Case 6
UserForm3.Show
End Select
End Sub

In questo codice, fatti i primi controlli per decidere se attivare una UserForm o no, si passa al costrutto Select Case nel quale si decide quale UserForm attivare.

 

Questo è tutto, spero.