ListBox (ActiveX): caricare la lista

ListBox (ActiveX): caricare la lista se i dati corrispondono ad un criterio, e caricare dati su più colonne. - dal 04/09/04 pagina vista: volte

Se disponiamo di database, o tabelle dati, o elenchi, sia disposti su una sola colonna che su colonne multiple, può capitare di voler caricare una ListBox con dati corrispondenti solo ad un determinato criterio, e non con tutti i dati contenuti nell'intervallo stabilito come fonte di origine dati. Cerco di spiegare subito con questa tabella di esempio:

Supponiamo di voler caricare la ListBox con tutti i nominativi contenuti nella colonna C, il cui valore associato (nella colonna D) è uguale a 20.

Oppure di voler caricare la ListBox con tutti i nominativi che risiedono a Roma.

O ancora di voler caricare la ListBox con i nomi di città che corrispondono ad una data, o rientrano in un intervallo di date.

Credo che ci siamo capiti, è chiaro che le esigenze di cercare un criterio in una colonna e prendere dati correlati con cui caricare una ListBox sono molto vari e ovviamente dipendono dai contenuti delle tabelle o elenchi o database che ognuno avrà, e dai risultati che vorremo ottenere in termini di combinazioni valore cercato-valore restituito.

Come vedete le operazioni da fare sono semplici, e altrettanto lo è il codice da scrivere. Vediamo subito una semplice routine che inseriremo in un modulo standard: facciamo cercare il valore 10 (criterio di ricerca) nella colonna D, nell'intervallo sotto specificato Range("D1:D10"), e trovata una cella col valore, prendiamo il nominativo che sarà sulla stessa riga, (sfruttando Offset), ma una colonna a sinistra rispetto al valore, e con AddItem inseriamo il nominativo nella ListBox. Il ciclo si completerà alla cella D10, e quella a lato è l'immagine della ListBox1 con i nominativi caricati

La sub CaricaLista dice: per ogni cella dell'intervallo D1:D10, se il valore della cella in quel momento letta è uguale a 10, allora aggiungi alla ListBox1 il valore che trovi nella cella a sinistra della cella in quel momento letta.

Precisazione: la ListBox è presa da "Strumenti di Controllo" e quindi "oggetto ActiveX", ed inserita sul foglio di lavoro 1; quando si usano istruzioni che fanno riferimento ad "oggetti ActiveX" inseriti sul foglio di lavoro, e le istruzioni sono in macro inserite su "moduli standard" (non quindi sul "modulo foglio") , bisogna definire anche a quale foglio l'"oggetto" appartiene; se avessimo scritto solo : ListBox1.AddItem Cell.Offset(0, -1).Value , avremmo generato un errore di run-time perchè le istruzioni poste in un "modulo standard", senza precisare su quale foglio si trova l'"oggetto" non saprebbero a quale oggetto "puntare". Se la sub CaricaLista l'avessimo posta sul modulo del foglio1, dove si trova la ListBox, sarebbe andata bene anche così.

Ora vediamo un 'esempio articolato, nel quale simuliamo di usare una data come criterio di ricerca, ma di voler cercare tutte le date comprese in un intervallo di date. Poi, anzichè caricare nella ListBox solo uno dei dati correlati, vorremo vedere nella lista le città correlate, i nominativi correlati, e gli importi correlati. Possiamo seguire diversi metodi per ottenere il nostro risultato, noi ne seguiremo due per quanto riguarda il come definire le date dell'intervallo (i criteri) :

Questo esercizio dovrebbe far capire anche ai meno esperti, come differenziare le istruzioni al variare delle nostre esigenze. Dobbiamo predisporre anche istruzioni che, ad ogni lancio di routine, provvedano a "pulire" (a rimuovere i dati) la ListBox, per lasciare posto ai nuovi dati che la lista ospiterà. Creiamo quindi una macro che intoteleremo Sub Rimuovi(), e che chiamereno ad ogni lancio della routine per l'aggiunta dei dati, vediamo prima questa macro:

Ed ora vediamo la routine che cercherà nella colonna A, tutte le date comprese tra due date scritte nel codice, e concateneremo i tre tipi di dati correlati (città, nome, importo). Useremo l'operatore di concatenamento & e inseriremo un trattino tra i dati. Da notare che usando la sintassi per le date, con la definizione della data posta tra cancelletti (#), la data va scritta nella forma inglese: mese/giorno/anno; provvederà Excel a interpretarlo come formato data italiano per compararlo con le date scritte nella colonna A

Cerchiamo quindi tutte le celle nell'intervallo A1:A10 in cui esistano date comprese tra il 25/03/2003 e il 30/12/03, trovata una data, con Offset prendiamo i valori nelle tre celle a destra e concateniamo i valori con cui carichiamo la ListBox, e questo è il risultato:

Vediamo ora l'ultima routine, che è un pò più complessa, ma non eccessivamente complicata. Dovremo sempre caricare i tre dati correlati, ma sfrutteremo tre colonne distinte nella ListBox, una per ogni dato correlato, anzichè concatenarli. Questo consente una migliore leggibilità dei dati, oltre ad insegnarci la procedura per caricare ListBox (ma anche per ComboBox) a colonne multiple, con dati provenienti da colonne del foglio, separate. Dovremo come prima cosa, modificare una proprietà della ListBox, la proprietà ColumnCount, impostando il suo valore al numero di colonne che vorremo utilizzare; nel nostro caso, essendo tre i dati di tre distinte colonne, imposteremo ColumnCount = 3 . Poi useremo nelle istruzioni il metodo AddItem, come nelle precedenti routine, ma anzichè caricare un solo dato per ogni riga della ListBox, ne dovremo caricare altri due; per questo aggiungeremo l'istruzione che sfrutta la proprietà List della ListBox. Questa proprietà necessita di due paramentri per lavorare, il numero di riga della ListBox dove dovranno essere caricati i dati, ed il numero di colonna, per sapere in quale delle tre colonne inserire i dati; la sua sintassi sarà quindi : ListBox1.List(0, 1). Ricordo che gli "indici" in vba iniziano sempre con zero ( 0 ), quindi lo zero identifica la prima posizione, quindi List(0 indica la primariga nella ListBox, e 1 indica la seconda colonna). Poichè non sapremo a priori quante righe di dati dovranno essere caricati, l'indice di riga della proprietà List dovremo renderlo variabile; ci affideremo per questo non ad un numero prefissato, ma ad una variabile che partendo dal valore 0 (zero), si incrementi di una unità, indicando in questo modo la successione nell'indice riga.

Per ottimizzare la nostra procedura, lavorando con intervalli di date, che potranno essere variabili, anzichè scrivere le date nel codice, useremo due InputBox con le quali reperiremo le due date dell'intervallo da controllare (criteri di ricerca). Ma vediamo la routine con relative spiegazioni:

Sub CaricaLista3()
Rimuovi 'si chiama la macro Rimuovi per pulire la ListBox
'sotto: si impostano le variabili datauno e datadue per reperire le date tramite inputbox
datauno = InputBox("Scrivi la data iniziale")
If datauno = "" Then Exit Sub 'se non scriviamo niente usciamo dalla routine
datadue = InputBox("Scrivi la data finale")
If datadue = "" Then Exit Sub 'se non scriviamo niente usciamo dalla routine

n = 0 'usiamo la variabile "n" come contatore di riga impostandolo a zero

'sotto: iniziamo il ciclo di ricerca nel range A1:A10 delle due date prese con le inputbox
For Each Cell In Range("A1:A10")

'sotto: controlliamo se la data presente nella cella in quel momento letta, è uguale o maggiore della 'data presa con "datauno" E uguale o inferiore a quella presa con "datadue", se si trova 'corrispondenza, allora:
If Cell.Value >= CDate(datauno) And Cell.Value <= CDate(datadue) Then

'sotto: usiamo tre variabili con le quali memorizziamo i valori contenuti nelle tre celle a destra 'rispetto alla cella con la data trovata
x = Cell.Offset(0, 1).Value   ' x sarà uguale a città
y = Cell.Offset(0, 2).Value   ' y sarà uguale a nominativo
z = Cell.Offset(0, 3).Value   ' z sarà uguale all'importo
'sotto: aggiungiamo (AddItem) i tre dati alla ListBox: con AddItem x carichiamo il dato x nella 'prima colonna delle tre disponibili: infatti il metodo AddItem inserisce un dato (per default) nella 'prima riga libera che trova nella ListBox e solo nella prima colonna, che è la colonna indice zero
Sheets(1).ListBox1.AddItem x

'sotto: inseriamo anche gli altri due dati ( y e z) indicando con List in quale colonna andranno 'posti, e come indice riga usiamo la variabile "n" che inizialmente è uguale a zero, quindi prima riga
Sheets(1).ListBox1.List(n, 1) = y
Sheets(1).ListBox1.List(n, 2) = z

'sotto: incrementiamo di uno la variabile "n" in modo che al ciclo successivo "n" indichi la riga 'successiva nell'istruzione List
n = n + 1
End If
Next
End Sub

Mi sembra che, analizzate le istruzioni e le spiegazioni, non sia difficile capire il meccanismo, che ne dite? Ed ora vediamo il risultato ottenuto nella ListBox.

Sarà possibile definire anche la larghezza delle colonne, usando la proprietà ColumnWidths ed assegnare una precisa dimensione in larghezza. La larghezza minima calcolata della colonna è 72 punti (2,54 cm, 1 pollice). E l'eventuale istruzione dovrà contenere i valori che indicano le larghezze, tutto su una riga, con i valori separati da punto e virgola e compresi tra due doppi apici. Per esempio per ottenere la spaziatura che vedete sopra, questa istruzione è stata posta a inizio della routine Sub CaricaLista3()