Lavorare con le ListBox o ComboBox a più colonne

 

Ribadiamo ancora il concetto che parlare di ListBox o di ComboBox il più delle volte è equivalente.

Sulle ListBox abbiamo già parlato in altre occasioni ma questa volta vale la pena riparlarne per scoprire nuove tecniche.

 

Ora vogliamo approfondire alcune tecniche che possiamo usare con le ListBox o con le ComboBox.

Questi sono i dati che useremo in questi esempi (oramai penso che avremmo acquisito il nuovo metodo di lavorare con gli intervalli senza più fare riferimento alla classica notazione A1):

ID Nome Cognome Indirizzo CAP Specialità
1 Luftikuss Michi Müllerweg 7 23456 Lüddelhausen
2 Muster Hannes Bonsaiweg 29 67890 Bonsai
3 maestro Lars Bonsaiweg 29 67890 Bonsai
4 insegnante Max Heimweg 9 98765 Schlumpfhausen
5 avvocato Michaela Bonsaiweg 29 67890 Bonsai
6 Mustermann Hans Musterweg 3 12345 Musterhausen
7 Müller Franz Bäckerstrasse 13 45678 Mühlhausen

 

Come vedremo qui di seguito esistono molti metodi per riempire una ListBox o una ComboBox.

Il discorso è leggermente articolato e spero di riuscire ad esporlo chiaramente.

Per comodità trascrivo l'ordine che ho seguito nell'esposizione di queste note.

  1. Riempimento di ComboBox e ListBox usando i cicli
  2. Riempimento di ComboBox e ListBox senza usare i cicli
  3. Lettura delle scelte effettuate in una ListBox ad una colonna
  4. Riempi e trasponi

 

Riempimento di ComboBox e ListBox usando i cicli

Questo metodo possiamo applicarlo sia con l'uso di matrici, sia leggendo i dati direttamente da un intervallo del foglio di Excel

Il riempimento di una ListBox usando degli opportuni cicli ed usando l'ormai famoso Additem può avvenire in uno dei seguenti modi.

In questo esempio usiamo una normale ListBox o ComboBox ad una sola colonna e la riempiamo con una matrice ad una sola dimensione che preleva i dati da una colonna di un Intervallo

Per un controllo ListBox o ComboBox ad una colonna usando le matrici

Dim intervallo As Range
Dim Righe, R, C
Dim Matrice()
With Range("A1").CurrentRegion ' in questo ciclo viene riempita una matrice ad una dimensione
Righe = .Rows.Count - 1
C = 3 ' viene scelta la colonna da memorizzare nella matrice
ReDim Matrice(1 To Righe)
For R = 1 To Righe
Matrice(R) = .Cells(R + 1, C)
Next
End With
ListBox1.Clear ' è buona norma resettare la ListBox prima di riempirla
For R = 1 To Righe
ListBox1.AddItem Matrice(R) ' riempimento della ListBox con un normale ciclo
Next

Per un controllo ListBox o ComboBox ad una colonna usando i dati di un intervallo

Se invece preleviamo i dati da un intervallo del foglio di Excel possiamo notare che la procedura è simile:

Dim Righe, Colonne, R
Dim Intervallo As Range
With Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Colonne = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(Righe, Colonne)
End With
With ListBox2
.RowSource = ""
.Clear
For R = 1 To Righe
.AddItem Intervallo(R, 2)
Next
End With

In VBA si ha la possibilità di usare anche ListBox e ComboBox a più colonne. Ma per usare queste occorrono alcuni accorgimenti.

Dalla guida in linea leggiamo queste notizie riguardanti queste voci.

 

Ma passiamo subito a degli esempi pratici.

Per un controllo ListBox o ComboBox a più colonne usando le matrici

Per usare una matrice a due dimensioni per un controllo ListBox o ComboBox eseguiamo queste operazioni:

Dim Intervallo As Range
Dim Righe, Colonne, R, C
Dim Matrice()
Worksheets("Foglio1").Select
With Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Colonne = .Columns.Count
ReDim Matrice(1 To Righe, 1 To Colonne)
For R = 1 To Righe
For C = 1 To Colonne
Matrice(R, C) = .Cells(R + 1, C)
Next
Next
End With
With ListBox1
.Clear
.ColumnCount = Colonne
.BoundColumn = 2
For R = 1 To Righe
.AddItem
For C = 1 To Colonne
ListBox1.List(R - 1, C - 1) = Matrice(R, C)
Next
Next
End With

Per un controllo ListBox o ComboBox a più colonne usando i dati di un intervallo

Se invece preleviamo i dati da un intervallo del foglio di Excel possiamo notare che la procedura è simile:

Dim Righe, Colonne, R, C
Dim Intervallo As Range
Worksheets("Foglio1").Select
With Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Colonne = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(Righe, Colonne)
End With
With ListBox1
.Clear
.ColumnCount = Colonne
.BoundColumn = 2
For R = 1 To Righe
.AddItem
For C = 1 To Colonne
.List(R - 1, C - 1) = Intervallo(R, C)
Next
Next
End With

 

Riempimento di ComboBox e ListBox senza usare i cicli

Invece di usare cicli, usando particolari proprietà e/o metodi è possibile evitare i cicli per riempire i controlli ListBox e ComboBox

Per un controllo ListBox o ComboBox ad una colonna usando le matrici

Una volta creata una matrice ad una sola dimensione è possibile trasferire i dati in essa memorizzati in uno di questi controlli

Dim Righe, Colonne, R, C
Dim Matrice()
Dim Intervallo As Range
With Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Colonne = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(Righe, Colonne)
End With
Righe = Range("A1").CurrentRegion.Rows.Count - 1
Colonne = Range("A1").CurrentRegion.Columns.Count
ReDim Matrice(1 To Righe)
C = 3
For R = 1 To Righe
Matrice(R) = Cells(R + 1, C)
Next
ListBox1.List() = (Matrice)

In questo esempio, per caricare il controllo ListBox con i valori di una matrice viene usata la proprietà List. In questo caso, la proprietà List non include indici tra le sue parentesi.

Attenzione

Se con questo metodo si tenta di caricare un controllo ListBox o ComboBox con una matrice a due dimensioni, verranno caricati solo i dati memorizzati nella prima colonna della matrice.

Nella costruzione della patrice provate a sostituire questo frammento di codice:

ReDim Matrice(1 To Righe)
C = 3
For R = 1 To Righe
Matrice(R) = Cells(R + 1, C)
Next

con quest'altro:

ReDim Matrice(1 To Righe, 1 To Colonne)
For R = 1 To Righe
For C = 1 To Colonne
Matrice(R, C) = Cells(R + 1, C)
Next
Next

L'azione non procura errori.

Per un controllo ListBox o ComboBox ad una colonna usando i dati di un intervallo

Usare un intervallo per riempire una ListBox o una ComboBox è simile a quel che abbiamo appena visto.

Ma al posto di usare la proprietà List è necessario usare la proprietà RowSource che specifica l'origine da cui proviene un elenco di un controllo ComboBox o ListBox

Dim Righe, R, C
Dim Intervallo As Range
Worksheets("Foglio1").Select
With Range("A1").CurrentRegion
Set Intervallo = .Offset(1, 1).Resize(Righe, 1)
End With
ListBox2.RowSource = Intervallo.Address

Attenzione:

Anche in questo caso, se l'intervallo è rappresentato da più di una colonna, nella ListBox verrà visualizzata solo la prima di queste colonne.

 

Per un controllo ListBox o ComboBox a più colonne usando una matrice

In VBA si ha la possibilità di usare anche ListBox e ComboBox a più colonne. Ma per usare queste occorrono alcuni accorgimenti.

Questo che segue potrebbe essere un esempio per riempire una ListBox usando dei cicli normali.

Dim Intervallo As Range
Dim Righe, Colonne, R, C
Dim Matrice()
With Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Colonne = .Columns.Count
ReDim Matrice(1 To Righe, 1 To Colonne)
For R = 1 To Righe
For C = 1 To Colonne
Matrice(R, C) = .Cells(R + 1, C)
Next
Next
End With
With ListBox1
.ColumnCount = Colonne
.BoundColumn = 2
.List() = Matrice
End With

Per un controllo ListBox o ComboBox ad una colonna usando i dati provenienti da un intervallo

Anche questa è simile a quella usata per un controllo ad una sola colonna:

Dim Righe, Colonne
Dim Intervallo As Range
With Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Colonne = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(Righe, Colonne)
End With
With ListBox1
.RowSource = ""
.ColumnCount = Colonne
.BoundColumn = 2
.RowSource = Intervallo.Address
End With

 

Lettura delle scelte effettuate in una ListBox ad una colonna

Per leggere l'elemento selezionato da una ListBox ad una colonna possiamo usare uno dei seguenti metodi:

lettura della voce scelta nella ListBox aiutati dalla proprietà Value

TextBox1 = ListBox2

lettura della voce aiutati dalla proprietà List che avrà come valore della proprietà ListIndex

R = ListBox2.ListIndex
TextBox1 = ListBox2.List(R)

 

Riempi e trasponi

E' possibile che qualche volta abbiamo la necessità di trasferire il contenuto di una matrice o di un intervallo ruotando i dati, ossia visualizzare i dati in colonne anzichè in righe come in questo esempio:

Migliavacca Rizzi Liberali Di Maggio ecc.
Luigi Carlo Franca Mario ecc.
VIA P. FIMIANI-LOC.TRIVIO VIA SABIN 34/2 VIA DEL TUSCOLANO 1 TERMINAL CONTENITORI MOLO VII ecc.
35031 84040 67030 48012 ecc.
Varena Noli Calcinatello Verzegnis ecc.
FR TO BO AL ecc.

 

A noi invece servono i dati disposti in quest'altra maniera:

Migliavacca Luigi VIA P. FIMIANI-LOC.TRIVIO 35031 Varena FR
Rizzi Carlo VIA SABIN 34/2 84040 Noli TO
Liberali Franca VIA DEL TUSCOLANO 1 67030 Calcinatello BO
Di Maggio Mario TERMINAL CONTENITORI MOLO VII 48012 Verzegnis AL
Scicchitano Andrea VIA SABIN, 34/2 10060 Peri NA
Maggi Massimo VIA DEL TUSCOLANO 1 20070 Formignana VI
Gerardini Silvano Z.I SAMBUCETO 61040 Villaretto Chisone LC
ecc. ecc. ecc. ecc. ecc. ecc.

 

Può capitare che per memorizzare dei dati in una matrice non possiamo dimensionare opportunamente la matrice con

ReDim Matrice(1 To Righe, 1 To Colonne)

perchè non conosciamo a priori la quantità di record da memorizzare. In questo caso dobbiamo rfidimensionare la matrice man mano che leggiamo i record con

ReDim Preserve Matrice(1 To Colonne, 1 To R)

Questo caso può succedere per esempio quando andiamo a leggere dei record residenti in un file di testo.

Riempi e trasponi con matrice

Vediamo con questo esempio cosa succede durante la lettura del file di testo e mandare i dati così letti in un controllo ListBox o ComboBox.

Dim Percorso, FileTesto, Filenum, Campi
Dim Riga, Matrice()
Dim Righe, Colonne, R, C
' viene determinato il nome del file da leggere comprensivo di percorso completo
Percorso = ThisWorkbook.Path & "\"
FileTesto = Percorso & "archivio.txt"
' quindi si verifica se il file esiste
If Dir(FileTesto) = "" Then
MsgBox "file non trovato"
Exit Sub
End If
' si inizia la lettura del file di testo riga per riga
Righe = 0
Filenum = FreeFile
Open FileTesto For Input As #Filenum
' nella prima riga del file di testo sono memorizzati i nomi dei campi
' da questi si determinano quanti campi deve contenere la matrice
Line Input #Filenum, Riga
Colonne = UBound(Split(Riga, "*")) + 1
' da qui continuo con la lettura del file di testo
While Not EOF(Filenum)
Line Input #Filenum, Riga
Campi = Split(Riga, "*")
R = R + 1
' dopo aver suddiviso le singole righe lette dal file di testo
' incremento la matrice e memorizzo i nuovi dati nella matrice
ReDim Preserve Matrice(1 To Colonne, 1 To R)
For C = 0 To UBound(Campi)
Matrice(C + 1, R) = Campi(C)
Next
Wend
Close #Filenum
' finita la lettura del file ho la matrice pronta ma con le dimensioni invertite
With ListBox1
.Clear
.ColumnCount = Colonne
.BoundColumn = 2
.Column() = Matrice
End With

Il codice esposto qui sopra è abbastanza commentato per cui mi esimo dall'aggiungere ulteriori commenti. Aggiungo solo queste poche note.

Alla fine della lettura del file di testo nella matrice abbiamo i record disposti nelle colonne anzichè nelle righe.

A noi invece serve che i record siano giustamente nelle righe.

La ListBox del VBA riesce ad accogliere tra le sue righe la matrice ruotata di 90° con la istruzione ListBox1.Column() = Matrice anzichè ListBox1.List() = Matrice

Repetita iuvant

  1. Normalmente i dati vengono memorizzati in una matrice a due dimensioni distribuendo i record tra le righe i campi tra le colonne.
  2. Ma in una matrice dinamica da dover ridimensionare con ReDim Preserve i dati vengono ruotati perchè in una matrice dinamica è possibile ridimensionare, senza perdere i dati, solo l'ultima dimensione:
    ReDim Preserve Matrice(1 To Colonne, 1 To R + 1)

Primo caso:

ReDim Matrice(1 To Colonne, 1 To Righe)
For R = 1 To Righe
For C = 1 To Colonne
Matrice(R, C) = Intervallo(R, C)
Next
Next

La matrice ha questo schema:

  Campo 1 Campo 2 Campo 3
Record 1      
Record 2      
Record 3      
Record 4      
Record 5      
Record 6      
Record 7      

secondo caso:

For R = 1 To Righe
ReDim Preserve Matrice(1 To Colonne, 1 To R)
For C = 1 To Colonne
Matrice(C, R) = Intervallo(R, C)
Next
Next

La matrice ha questo schema:

  Record 1 Record 2 Record 3 Record 4 Record 5 Record 6 Record 7
Campo 1              
Campo 2              
Campo 3              

 

 

Riempi e trasponi i dati che vengono da un intervallo

Ma vediamo come comportarci se la stessa situazione la troviamo in un foglio di Excel.

Questo metodo è un po' più complesso, nella sintassi da usare rispetto a quelli fin qui visti.

Dim Righe, Colonne
Dim Intervallo As Range
Worksheets("Foglio4").Select
With Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Colonne = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(Righe, Colonne)
End With
With ListBox1
.ColumnCount = Colonne
.BoundColumn = 2
.List() = Application.Transpose(Intervallo)
End With

 

Eliminazione di una riga da un elenco della ListBox

L'istruzione richiede che sia indicata la riga da eliminare:

Riga = ListBox1.ListIndex

Con questa istruzione leggiamo la riga attiva della Listbox.

Se nessuna riga è selezionata o la ListBox è vuota, viene generato un errore per cui è bene mettere prima di questa istruzione un controllo del genere:

If ListBox1.ListIndex = -1 Then Exit Sub

Ne consegue che nella routine che esegue questo compito si scrivano nell'ordine queste istruzioni:

ListBox1.RemoveItem (Riga)

In questo caso l'elenco della ListBox si compatta automaticamente senza codice aggiuntivo e con questo è tutto per questa funzionalità.

Questa è la sequenza completa delle istruzioni:

If ListBox1.ListIndex = -1 Then Exit Sub
Riga = ListBox1.ListIndex
ListBox1.RemoveItem (Riga)