ComboBox

Possiamo disporre di diversi tipi di ComboBox:

disegnata sul Foglio di excel
- dalla barra degli strumento Moduli
- dalla barra Strumenti di Controllo
disegnata in una UserForm

Le ComboBox disegnate sul foglio

Per la ComboBox presa dai Moduli

Una volta disegnata sul foglio

scegliere formato controllo facendo clic destro sulla ComboBox

nella finestra che si apre scegliere la scheda controllo

in intervallo di input scrivere o selezionare l'intervallo da cui prelevare i dati da mettere nella lista

in collegamento cella scrivere o selezionare la cella dove depositare il dato scelto

Questo controllo, quando viene usato, non restituisce il nome selezionato bensì il numero di indice che il nome selezionato occupa nella lista: se si sceglie il primo elemento restituisce nella cella collegata il numero 1, se si sceglie il terzo elemento restituisce il numero 3 e così via.

Per visualizzare correttamente il valore scelto nella ComboBox occorre usare la funzione =INDICE()

Se nella finestra Formato controllo abbiamo specificato che in H2:H17 abbiamo l'elenco dei nomi da inserire nella ComboBox e abbiamo designato che la C1 è cella collegata, la formula per visualizzare il nome selezionato nella giusta cella che nel nostro caso è la C4 sarà: =INDICE(H2:H17;C1). Altri eventuali riferimenti verranno cercati col classico CERCA.VERT.

Questo sarà lo schema da usare nelle celle della mascherà che dovrà accogliere i dati:

cella C1: =INDICE(H2:H17;C1)

cella C3: =SE(C1="";"";CERCA.VERT(C1;H2:L17;2;FALSO))

cella C5: =SE(C1="";"";CERCA.VERT(C1;H2:L17;3;FALSO))

cella C7: =SE(C1="";"";CERCA.VERT(C1;H2:L17;4;FALSO))

cella C9: =SE(C1="";"";CERCA.VERT(C1;H2:L17;5;FALSO))

cella C11: =SE(C1="";"";CERCA.VERT(C1;H2:L17;5;FALSO))

Qui possiamo vedere un piccolo esempio di utilizzo di questa ComboBox.

Per la ComboBox presa da Strumenti di controllo

Una volta disegnato il controllo sul foglio

scegliere dalla barra Strumenti di controllo il pulsante Proprietà (l'icona con la manina)

si apre la finestra proprietà

in questa finestra eseguire queste operazioni

in ListFillRange scrivere l'intervallo da cui prendere i dati da mettere nella lista: nell'esempio che stiamo portando avanti scriviamo H2:H17. Se l'intervallo di origine si trova in un altro foglio dobbiamo scrivere anche il nome di quel foglio. Es: Foglio2!A1:A15

in LinkedCell scrivere la cella dove deve essere scritto il dato selezionato Es: C1

Questo controllo, contrariamente a quello preso dai Moduli, quando viene utilizzato, restituisce, nella cella indicata in LinkedCell, il nome del dato scelto, perciò in LinkedCell possiamo indicare direttamente la cella che deve visualizzare il dato escludendo così l'uso della funzione =INDICE(H2:H17;C1) usata con l'altro controllo.

Finita questa operazione, per usare la ComboBox, bisogna disabilitare la Modalità progettazione (l'icona con squadra e riga) presente sulla barra degli strumenti.

Nella cella C4 non è più necessario mettere la funzione INDICE in quanto il dato prelevato dalla ComboBox viene scritto direttamente nella cella. Per il resto delle celle rimangono valide le formule appena viste:

cella C3: =SE(C1="";"";CERCA.VERT(C1;H2:L17;2;FALSO))

cella C5: =SE(C1="";"";CERCA.VERT(C1;H2:L17;3;FALSO))

cella C7: =SE(C1="";"";CERCA.VERT(C1;H2:L17;4;FALSO))

cella C9: =SE(C1="";"";CERCA.VERT(C1;H2:L17;5;FALSO))

cella C11: =SE(C1="";"";CERCA.VERT(C1;H2:L17;5;FALSO))

Per maggior chiarezza anche per questa ComboBox ho preparato un modellino simile al precedente.

Per questo controllo è possibile aiutarsi col VBA

Se l'uso che si può fare di questa ComboBox con le semplici impostazioni non ci soddisfano possiamo facilmente far uso di istruzioni VBA evitando di impostare le proprietà ListFillRange, LinkedCell o entrambe.

I passi da seguire per usare la ComboBox da VBA sono due:

L'inizializzazione della ComboBox usando l'evento Workbook_Open nel modulo relativo a ThisWorkbook
Decidere dove mandare il dato selezionato usando l'evento Change della ComboBox

Primo esempio: usare la ComboBox per gestire una maschera gia preimpostata

L'aspetto del foglio non cambia rispetto a quelli già visti precedentemente.

Inizializzazione della ComboBox

Usando l'evento Workbook_Open nel modulo relativo a ThisWorkbook

Private Sub Workbook_Open()
Dim NumR, NumC, R
Dim Intervallo As Range
With Worksheets("Foglio4").Range("H1")
      NumR = .CurrentRegion.Rows.Count
      NumC = .CurrentRegion.Columns.Count
      Set Intervallo = .CurrentRegion.Offset(1, 0).Resize(NumR - 1, NumC)
End With
With Worksheets("Foglio4").ComboBox1
      .Clear
      For R = 1 To Intervallo.Rows.Count
            .AddItem (Intervallo(R, 1))
      Next
End With
End Sub

E' buona norma, anche se a volte può sembrare inutile, cancellare il contenuto della ComboBox prima di provvedere al suo popolamento per evitare che azioni o eventi imprevisti continui ad incrementarne e duplicare gli elementi.

Uso della ComboBox

Usando l'evento Change della ComboBox

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = -1 Then Exit Sub
Range("C1") = ComboBox1.List
End Sub

Il codice da usare per scrivere il dato scelto dalla ComboBox nella cella C4 si riduce al minimo e lo poniamo nell'evento Change della ComboBox1 grazie alle formule che abbiamo lasciato nel foglio di calcolo. Infatti per risparmiare codice da scrivere nel Private Sub ComboBox1_Change(), nelle celle sottostanti la C4 (la C6, C8, C10, C12) possiamo usare le formule che abbiamo usato precedentemente.

cella C6: =SE(C4="";"";CERCA.VERT(C4;H2:L17;2;FALSO))

cella C8: =SE(C4="";"";CERCA.VERT(C4;H2:L17;3;FALSO))

cella C10: =SE(C4="";"";CERCA.VERT(C4;H2:L17;4;FALSO))

cella C12: =SE(C4="";"";CERCA.VERT(C4;H2:L17;5;FALSO))

Una volta aperta la cartella e selezionato il foglio4 è possibile usare la ComboBox che grazie al codice appena visto è già pronta.

Ma questa è una variante quasi inutile alla impostazione fatta con la finestra proprietà del controllo stesso se dobbiamo usare la maschera che ho precedentemente illustrata nella parte superiore dell'esempio. Infatti ci risparmia solo di eseguire le impostazioni da fare nella finestra Proprietà relativa alla ComboBox.

Secondo esempio: uso della ComboBox per gestire una tabella dinamica

Ben più utile ed interessante è l'uso del VBA se, invece di indirizzare l'output della ComboBox in una maschera, vogliamo creare un elenco.

In questo caso si potrebbe usare questo codice scritto sempre nell'evento Change della ComboBox1.

Anche in questo caso usiamo i due eventi appena visitati.

L'inizializzazione della ComboBox usando l'evento Workbook_Open nel modulo relativo a ThisWorkbook
Decidere dove mandare il dato selezionato usando l'evento Change della ComboBox

Inizializzazione della ComboBox

Usando l'evento Workbook_Open nel modulo relativo a ThisWorkbook

Private Sub Workbook_Open()
Dim NumR, NumC, R
Dim Intervallo As Range
With Worksheets("Foglio3").Range("H1")
NumR = .CurrentRegion.Rows.Count
NumC = .CurrentRegion.Columns.Count
Set Intervallo = .CurrentRegion.Offset(1, 0).Resize(NumR - 1, NumC)
End With
With Worksheets("Foglio3").ComboBox2
.Clear
For R = 1 To Intervallo.Rows.Count
.AddItem (Intervallo(R, 1))
Next
End With
End Sub

Per la ComboBox continuiamo ad usare l'evento Change della ComboBox

Private Sub ComboBox2_Change()
Dim R, R1, C, NumR, NumC
Dim Intervallo As Range
If ComboBox2.ListIndex = -1 Then Exit Sub
'=============================
R = 18
While Cells(R, 1) <> ""
    R = R + 1
Wend
Cells(R, 1) = ComboBox2.Text
'=============================
With Range("H1").CurrentRegion
    NumR = .Rows.Count
    NumC = .Columns.Count
    Set Intervallo = .Offset(1, 0).Resize(NumR - 1, NumC)
End With
'=============================
For R1 = 1 To Intervallo.Rows.Count
    If Intervallo(R1, 1) = Cells(R, 1) Then
        For C = 2 To Intervallo.Columns.Count
            Cells(R, C) = Intervallo(R1, C)
        Next
        Exit For
    End If
Next
End Sub

In questo caso il codice può sembrare leggermente più complesso di quello precedente, ma se lo analizziamo vedremo che non lo è affatto.

Dopo le solite dichiarazioni delle variabili ed aver controllato se è stata eseguita una scelta nella ComboBox, vengono eseguite queste operazioni:

si cerca la prima cella disponibile a partire dalla prima cella della riga 18
trovata la cella vi si scrive il contenuto della ComboBox
viene determinato l'intervallo che contiene i dati di origine (nel nostro caso l'intervallo che è associato alla cella H1)
in questo intervallo viene cercato il dato appena scritto nella nuova cella
trovato il dato si copiano tutti gli altri dati correlati all'elemento scelto

La ComboBox che viene usata in una Userform

Concludiamo col terzo caso: la ComboBox usata su una UserForm.

In questo caso occorre necessariamente recarsi in ambiente VBA con uno dei tanti mezzi di cui disponiamo: Alt + F11, dal menù Strumenti => Macro => Visual Basic Editor, oppure scegliendo l'icona "Visual Basic Editor" dalla Barra degli strumenti Visual Basic se è aperta.

In questo ambiente vediamo alcune finestre:

Progetto - VBA Project
Proprietà
Finestra principale

Nella finestra Progetto - VBA Project facciamo clic destro col mouse e, dal menù contestuale scegliamo "inserisci => UserForm" per inserire un nuovo modulo col suo nome di default UserForm1

Assieme alla UserForm dovremmo vedere anche la finestra Casella degli strumenti. Se questa non è visualizzata clicchiamo sul pulsante Casella degli strumenti.

Per il nostro progettino cerchiamo di costruire una userform simile a questa dove abbiamo usato:

una ComboBox
5 caselle di testo
2 CommandButton
alcune Label che servono solo come descrizione (ininfluenti per il funzionamento del piccolo applicativo)

Da questa Casella degli strumenti scegliamo i Controlli che vogliamoe li disegnamo nella UserForm.

Per attivare la funzionalità di questa UserForm scriviamo tutto il codice nel modulo relativo alla UserForm usando gli appropriati Eventi di alcuni oggetti in essa contenuti:

L'evento Initialize della UserForm
L'evento Change della ComboBox1
L'evento Change della TextBox1
L'evento Click del CommandButton1
L'evento Click del CommandButton2

Siccome molti di questi eventi non sono scatenati solo dalle azioni dell'utente ma anche dal mutare delle condizioni sia dellaComboBox1 e della TextBox1 durante l'esecuzione delle varie routines, occorre prestare particolare attenzione nel bloccare le azioni che da questi eventi vengono eseguite se veniamo a trovarci in particolari situazioni.

Innanzi tutto dichiaro pubblica a livello di questo modulo la variabile Intervallo che userò negli eventi della UserForm, della TextBox1 e, forse, della CommandButton1
Initialize della UserForm per caricare la ComboBox in essa contenuta e per definire l'intervallo di origine da cui prelevare i dati
Change dell ComboBox1 per scrivere nella prima TextBox (TextBox1) il nome scelto
Change della TextBox1 per scrivere nelle alter TextBox gli altri dati correlati al nome scelto
Click del CommandButton1 per scrivere sul foglio tutti i dati del nome scelto
Click del CommandButton2 per finire la procedura

Con questo sistema molti degli eventi interferiscono con altri e vedremo nel codice che sto per presentare.

L'evento Initialize della UserForm

E' utile dichiarare la variabile Intervallo che memorizzerà l'intervallo di origine dei dati che useremo in questo esempio perchè a questo intervallo faremo riferimento in più di una routine.

La visibilità di questa variabile dovrà essere a livello del modulo relativo alla UserForm, perciò va dichiarata tra le prime righe del modulo, fuori di qualsiari routine:

Dim Intervallo As Range

Di seguito vediamo come sono organizzate le routines

Private Sub UserForm_Initialize()
Dim NumR, NumC, R
With Worksheets("Foglio3").Range("H1")
    NumR = .CurrentRegion.Rows.Count
    NumC = .CurrentRegion.Columns.Count
    Set Intervallo = .CurrentRegion.Offset(1, 0).Resize(NumR - 1, NumC)
End With
'=============================
With ComboBox1
    .Clear
    For R = 1 To Intervallo.Rows.Count
        .AddItem (Intervallo(R, 1))
    Next
End With
End Sub

Con questa compiamo due azioni:

definiamo l'intervallo di origine, che si trova nel foglio3 a partire dalla cella H1 e che poi servirà anche in altri posti
popoliamo la ComboBox

L'evento Change della ComboBox1

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = -1 Then
TextBox1 = ""
Exit Sub
End If
'=============================
TextBox1 = ComboBox1.Text
End Sub

La comboBox può avere due stati:

la ListIndex pari a -1, nessuna selezione in atto: in questo caso viene cancellato anche il contenuto della TextBox1
la ListIndex è pari al valore del dato scelto: in questo caso il dato scelto viene scritto anche nella TectBox1

L'evento Change della TextBox1

Private Sub TextBox1_Change()
Dim R, R1, C
Dim CTRL As Control
If ComboBox1.ListIndex = -1 Then
    For Each CTRL In Controls
        If (TypeName(CTRL) = ("TextBox")) Then
           CTRL = ""
           C = C + 1
       End If
    Next
    Exit Sub
End If
'=============================
R = 18
While Cells(R, 1) <> ""
    R = R + 1
Wend
For R1 = 1 To Intervallo.Rows.Count
    If Intervallo(R1, 1) = TextBox1 Then
        For C = 2 To Intervallo.Columns.Count
            Controls("textbox" & C) = Intervallo(R1, C)
        Next
        Exit For
    End If
Next
End Sub

Anche in questo caso valutiamo la condizione della ComboBox

la ListIndex pari a -1, nessuna selezione in atto: in questo caso vengono cancellate tutte le TextBox
la ListIndex è pari al valore del dato scelto: in questo caso:
- si determina la prima riga disponibile nell'intervallo di destinazione
- si cerca il dato presente nella TextBox1 nell'elenco di origine
- trovato il dato (il Cognome) se ne scrivono tutti i riferimenti, presenti a partire dalla seconda colonna, in tutte le TextBox a partire dalla seconda

L'evento Click del CommandButton1

Private Sub CommandButton1_Click()
Dim R, R1, C
Dim CTRL As Control
If ComboBox1.ListIndex = -1 Then Exit Sub
'=============================
R = 1
    While Cells(R, 1) <> ""
    R = R + 1
Wend
'=============================
'Cells(R, 1) = ComboBox1.Text
'For R1 = 1 To Intervallo.Rows.Count
'    If Intervallo(R1, 1) = Cells(R, 1) Then
'         For C = 2 To Intervallo.Columns.Count
'             Cells(R, C) = Intervallo(R1, C)
'         Next
'         Exit For
'     End If
'Next
'=============================
C = 1
For Each CTRL In Controls
    If (TypeName(CTRL) = ("TextBox")) Then
        Cells(R, C) = CTRL.Text
        C = C + 1
    End If
Next
ComboBox1.ListIndex = -1
End Sub

Potete notare che nella routine c'è una parte di codice commentata. Non è una distrazione ma vuol mostrare una alternativa al codice che segue immediatamente sotto.

Altro controllo sullo stato della ListIndex della ComboBox

la ListIndex pari a -1, nessuna selezione in atto: non facciamo succedere nulla
la ListIndex è pari al valore del dato scelto: in questo caso:
- determiniamo la prima cella disponibile ad ospitare i nostri dati
- trasferiamo i dati sul foglio. A tal scopo possiamo optare tra due opzioni:
  - rileviamo i dati da trasferire direttamente dalla tabella di origine (la serie di istruzioni commentata)
  - trasferiamo i dati già contenuti nelle TextBox

L'evento Click del CommandButton2: la fase conclusiva

Private Sub CommandButton2_Click()
Dim Risp
If ComboBox1.ListIndex = -1 Then
Unload UserForm1
Exit Sub
End If
Risp = MsgBox("sicuro di voler uscire", vbExclamation + vbYesNo, "Attenzione")
If Risp = vbYes Then Unload UserForm1
End Sub

Ancora un controllo sullo stato della ListIndex della ComboBox

la ListIndex è pari a -1, si chiude la UserForm ed il lavoro finisce

la ListIndex è pari al valore del dato scelto: in questo caso si sta tentando di chiudere la UserForm senza scaricare gli ultimi dati selezionati. In questo caso si pone una domanda all'utente se veramente vuole interrompere il lavoro. In caso affermativo si termina il lavoro, in caso contrario si lascia la UserForm aperta per continuare il lavoro.

Questo potrebbe essere un eventuale aspetto del foglio di lavoro

Allego il file che ho usato per questa esercitazione: combobox.zip