ListBox a più colonne, con selezione e scelta delle colonne da caricare. - pagina vista: volte

Difficoltà: media

Una recente richiesta è alla base di questo esercizio: come poter scegliere e selezionare le colonne di una tabella impostata su molte colonne (20 e più), colonne da visualizzare in una ListBox posta su UserForm.

Esiste comunque un numero massimo di colonne visualizzabili in una ListBox, e questo limite è 10 ( il numero massimo di colonne consentite è 10 (da 0 a 9 come valori di indice)).

La soluzione può essere affrontata in due diversi modi:  scegliendo le colonne di una tabella dati sul foglio di lavoro che vorremo visualizzare, o nascondendo le colonne che non vorremo visualizzare nella ListBox. In entrambi i casi dovremo usare una matrice per memorizzare i numeri di colonna su cui agire. Presento la soluzione di nascondere le colonne che non vogliamo visualizzare, così ci esercitiamo anche su questo aspetto: nascondere o scoprire colonne.

In entrambe le soluzioni , esiste comunque un problema di indici : infatti  la proprietà List, necessaria ad aggiungere righe di dati in una ListBox, (sfruttando il metodo AddItem), richiede due argomenti: il numero (indice) di riga della ListBox stessa nella quale caricare un dato, e il numero (indice) di colonna della ListBox (con ListBox a più colonne, necessaria) nella quale inserire il dato stesso, in una istruzione simile a:

  • ListBox1.List(numero riga, numero colonna) - sintassi simile all'uso che si fa con Cells(nriga, ncolonna)

Le cose sono facili da gestire, quando si caricano colonne conseguenti di un foglio di lavoro: basta infatti mantenere la stessa progressione: iniziando da una determinata colonna del foglio di lavoro; si inizia a caricare la ListBox dalla prima colonna della ListBox (indice 0), e si prosegue scalando di colonna sia del foglio (+1) sia della ListBox con un ciclo For Next, (oppure si caricano colonne non conseguenti, ma fisse, sempre le stesse, cioè sempre gli stessi numeri colonna, quindi indici predefiniti).

Ma qui non sapremo a priori nè quali fra le colonne vorremo caricare nè da quale indice (numero di) colonna iniziare; diventa necessario trovare un sistema che identifichi un dato variabile (l'indice colonna del foglio, cioè il numero di colonna) facendolo corrispondere ad un dato fisso (l'indice colonna della ListBox) ma incrementabile; abbiamo bisogno di specificare una cosa del genere, esempio: nella colonna 1 della ListBox dovranno essere caricati i dati della Colonna 5 del foglio, nella colonna 2 della ListBox vogliamo i dati della colonna 8, ecc. ecc.

Un'altro aspetto da non trascurare, è che non potremo sapere quanto sarà lungo (in termini di righe), l'elenco di dati da caricare e potremo avere colonne con elenchi di lunghezza diversa, ma questo è  facilmente superabile: basta contare le righe occupate dell'ActiveSheet.UsedRange (con celle che contengano solo dati) per essere sicuri che comprenderemo sicuramente tutte le righe di tutta la larghezza dell'area dati (di tutte le colonne occupate) Altrettanto faremo con le colonne, per conoscere quante colonne dati compongono tutta l'area. Potremo usare una istruzione come questa, affidando a due variabili pubbliche i valori così reperiti:

  • UR = ActiveSheet.UsedRange.SpecialCells(xlLastCell, xlNumbers).Row 'con UR otteniamo il numero dell'ultima riga  occupata
    UC = ActiveSheet.UsedRange.SpecialCells(xlLastCell, xlNumbers).Column 'con UC otteniamo il numero dell'ultima colonna occupata (verso destra)

Questa soluzione (dell'UsedRange) non andrà bene con quei fogli dove, oltre alla tabella contenente dati, ci saranno pure altre aree di dati come tabelline di conversione, o celle di appoggio, o altro, perchè verrebbero considerate nell'UsedRange; in questo caso sarebbe meglio usare il riferimento preciso del numero dell'ultima colonna dell'area dati.

Il problema degli indici lo risolviamo (nascondendo le colonne che non vogliamo caricare) leggendo le intestazioni di colonna delle colonne visibili (Colonne NON nascoste) nell'area dati,  ricavare il loro numero indice da memorizzare in una matrice. Questo ci consentirà di ottenere un sicuro "allineamento" tra gli indici di colonna della ListBox e gli indici di matrice , che conterranno però i giusti numeri di colonna, (anche se sfalsati, non conseguenti) scelti. La stessa procedura di leggere le intestazioni di colonna per ricavarne il numero indice di colonna lo dovremmo eseguire anche nel caso che si fosse scelto di non nascondere colonne.

Questa soluzione non è l'unica seguibile (potremmo sfruttare anche il metodo Union, o tutto con matrici), ma ognuno seguirà la procedura a lui più consona, io presento questa.

Vediamo intanto un'immagine di una possibile tabella; ho predisposto 15 colonne (ma potranno essere di più) ed inizio la tabella dalla riga 3 dove ho posto le "intestazioni di colonna"; i dati usati sono semplici numeri, ma potrà essere testo, o formule, l'importante è predisporre comunque un'intestazione per ogni colonna, e non è neppure necessario che le colonne siano tutte conseguenti, potremo avere ad esempio la colonna 5 vuota o altre, tanto se useremo l'UsedRange verranno comunque comprese tutte quelle occupate da dati. L'importante sarà che tutte le colonne inizino da una stessa riga.

Ricordo ancora che per quante colonne compongano la nostra tabella, non sarà possibile visualizzarne nella ListBox più di 10.

Le procedure.

All'apertura della UserForm, evento Activate, carichiamo una ListBox (la ListBox2) con i nomi delle "intestazioni di colonna" reperiti con un ciclo For Each Next, sulla riga 3, e fino all'ultima  colonna reperita con UC, questa la routine:

  • Private Sub UserForm_Activate()
    UR = ActiveSheet.UsedRange.SpecialCells(xlLastCell, xlNumbers).Row
    UC = ActiveSheet.UsedRange.SpecialCells(xlLastCell, xlNumbers).Column
    Label3.Caption = UR      'nella label3 facciamo apparire il numero dell'ultima riga contata con UR
    Label4.Caption = UC      'nella label4 facciamo apparire il numero dell'ultima colonna contata con UC
    For Each col In ActiveSheet.Range(Cells(3, 1), Cells(3, UC))     'ciclo per caricare i nomi dei campi nella listBox2 con 'il metodo AddItem
    ListBox2.AddItem col.Value      'aggiungiamo i nomi delle intestazioni di colonna
    Next
    End Sub

e vedremo questo:

Lo scopo dell'aver caricato i nomi che corrispondono alle colonne è evidente: potremo selezionare i nomi delle colonne che vorremo nascondere, e che tramite un click sul nome (evento Click della ListBox2), invieremo nella ListBox3 (a destra nella foto). Una volta selezionate le colonne da nascondere, useremo il pulsante "Nascondi Colonne" che leggendo i nomi nella ListBox3, reperisce i rispettivi numeri di colonna delle celle corrispondenti al nome letto.

Solo dopo aver nascosto le colonne, potremo premere il pulsante "Carica Lista", che provvede, leggendo i nomi delle colonne visibili (Hidden = False), a caricare la matrice "iCol" che memorizzerà i numeri (gli indici) delle colonne visibili, e a seguire, con un doppio ciclo For Next, a caricare nella ListBox1 (quella sotto, nella foto) i dati contenuti nelle colonne scelte. Il pulsante "Scopri Colonne" servirà alla fine per rendere di nuovo visibili le colonne nascoste in precedenza. Questo sotto  è un esempio con 6 colonne nascoste: nella ListBox3 i "nomi" delle celle delle colonne da nascondere, e dopo aver premuto "Nascondi Colonne" e successivamente "Carica Lista", questo è il risultato:

il foglio come si presenta con le colonne nascoste:

Ciò che faremo con i dati caricati nella ListBox1, non riguarda questo esercizio, ma non sarà difficile per esempio, copiare  i dati così raccolti su altro foglio, seguendo le indicazioni presenti sugli altri articoli sulle ListBox in questa stessa sezione.

Vediamo ora le routine dei vari eventi:

queste le istruzioni per trasferire i nomi delle colonne nella ListBox3, ad ogni Click nella ListBox2; contemporaneamente eseguiamo una differenza tra il numero delle colonne totali contate (Label4) e il numero delle colonne che si troveranno nella ListBox3; questo ci dirà quante colonne restano da visualizzare (non dovremo superare le 10 colonne) e lo vedremo nella Label8:

  • Private Sub ListBox2_Click()
    ListBox3.AddItem ListBox2.Text
    Label8.Caption = Label4.Caption - ListBox3.ListCount
    End Sub

ora vediamo le istruzioni inserite nell'evento Click del pulsante "Nascondi colonne". Ho inserito un istruzione che controlla che la ListBox3 contenga almeno un nome di colonna, e ci avvisi, uscendo dalla sub, se la ListBox3 è vuota; poi inizio un doppio ciclo che leggendo i nomi ora nella ListBox3, scorra le celle della riga 3, dalla prima colonna (la 1) e fino all'ultima (la UC) dell'area dati, e se trova una cella corrispondente al nome in quel momento letto, ne legge il numero di colonna (variabile "g") e nasconde la colonna così identificata (dal suo numero indice):

  • Private Sub CommandButton2_Click()
    If IsNull(ListBox3.List(0, 0)) Then     'se il primo indice di List è nullo(vuoto), allora si avvisa e si esce dalla sub
    MsgBox "Seleziona le colonne da nascondere"
    Exit Sub
    End If
    Dim cell As Object
    P = ListBox3.ListCount      'contiamo quanti sono i nomi presenti nella ListBox3 e li memorizziamo nella variabile "P"
    For K = 0 To P - 1        'ora iniziamo un ciclo che scorra gli indici nella ListBox (l'indice inizia da zero, non da 1) e giri 'fino al valore rappresentato da P meno 1 (se avremo 6 nomi nella ListBox3, il loro indice sarà da 0 a 5)
    For Each cell In ActiveSheet.Range(Cells(3, 1), Cells(3, UC))     'per ogni cella nell'intervallo specificato
    If cell.Value = ListBox3.List(K, 0) Then      'se il valore nella cella ora letta è uguale al nome rappresentato dal 'contatore K di List (il valore corrispondente al numero indice letto in questo momento del ciclo K), allora
    g = cell.Column       'si assegna alla variabile "g" il numero di colonna a cui la cella appartiene
    ActiveSheet.Columns(g).Hidden = True      'e si nasconde quella colonna
    End If
    Next: Next
    End Sub

Una volta nascoste le colonne, potremo premere il pulsante "Carica Lista" per aggiungere nella ListBox1 il contenuto di tutte le righe (fino alla riga UR) di tutte le colonne dell'intervallo fino a UC, rimaste visibili. Iniziamo la routine contando le colonne dell'intervallo fino a UC rimaste visibili: usiamo un contatore "cont" che si incrementa di 1 ad ogni colonna rimasta visibile (quindi Hidden = False), se il numero di "cont" sarà superiore a 10, si esce dalla routine e si avvisa con un messaggio; sarà necessario selezionare altri nomi di colonne da nascondere nella ListBox2 fintanto che leggeremo nella Label8 un limite massimo di 10 (o inferiore), quindi premeremo di nuovo il pulsante "Nascondi Colonne", e quindi di nuovo il pulsante "Carica Lista".

  • Private Sub CommandButton1_Click()
    For Each cell In ActiveSheet.Range(Cells(3, 1), Cells(3, UC))
    If cell.Columns.Hidden = False Then
    cont = cont + 1
    End If
    Next
    If cont > 10 Then
    MsgBox "Il numero di " & cont & " colonne è maggiore di quelle consentite (10)"
    Exit Sub
    End If
    ListBox1.ColumnCount = cont     'ora impostiamo il numero di colonne da visualizzare nella ListBox1
    Dim iCol() As Variant      'ora si imposta la matrice "iCol" di tipo Variant
    Dim A
    ReDim iCol(1 To cont)     'dimensioniamo la matrice indicando il numero di elementi che la comporranno (da 1 e fino a '"cont") (matrice dinamica)
    A = 1     'inizializiamo il contatore "A" della matrice a 1
    For Each cell In ActiveSheet.Range(Cells(3, 1), Cells(3, UC))     'e per ogni cella nell'intervallo specificato
    If cell.Columns.Hidden = False Then      'se la colonna della cella ora letta è visibile, allora
    iCol(A) = cell.Column       'aggiungiamo alla variabile "iCol" nell'indice di matrice corrispondente ad "A", il numero di 'colonna ora letta
    A = A + 1      'quindi si incrementa di 1 il contatore "A"
    End If
    Next       'e si passa alla cella successiva
    'sotto: ora si inizia un doppio ciclo For Next: il ciclo esterno si occupa di definire quante righe scorrere, e dovremo 'iniziarlo da zero perchè lo stesso contatore "N" servirà anche per impostare l'indice iniziale di List nella ListBox che 'inizia appunto da zero e non da 1
    For N = 0 To UR - 1
    L = iCol(LBound(iCol))      'con "L" prendiamo il numero di colonna memorizzato nel limite inferiore (LBound) della 'matrice "iCol", e ci serve per definire quale colonna ("L") della cella N + 3 (cioè dalla riga 3 del foglio) iniziare il 'metodo AddItem
    ListBox1.AddItem ActiveSheet.Cells(N + 3, L)
    'ora inizia il ciclo interno che scorrerà i numeri di colonna memorizzati nella matrice "iCol", caricando la ListBox1 'tramite List(indice riga, indice colonna) con i valori contenuti nelle celle(riga N+3, colonna "A" di "iCol")
    For A = LBound(iCol) To UBound(iCol)
    ListBox1.List(N, A - 1) = Cells(N + 3, iCol(A))
    Next: Next
    End Sub

Il pulsante "Scopri Colonne" andrà usato alla fine delle nostre operazioni con le ListBox, oppure la routine sotto potremo usarla alla chiusura della UserForm sfruttando il suo evento QueryClose:

  • Private Sub CommandButton3_Click()
    With ActiveSheet
    .Columns.Hidden = False      'rendiamo visibile ogni colonna che è nascosta sul foglio attivo
    End With
    End Sub

Ho aggiunto un pulsante "Pulisci liste 1 e 3" per azzerare le ListBox 1 e 3 nel caso si volesse ripetere il caricamento con altre colonne. Questa la routine che sfrutta il metodo RemoveItem:

  • Private Sub CommandButton4_Click()
    For B = 1 To 3 Step 2      'uso un ciclo che salta la ListBox2 per rimuovere i dati solo dalle ListBox 1 e 3
    N = Me.Controls("ListBox" & B).ListCount - 1
    If N >= 0 Then
    For z = N To 0 Step -1      'il ciclo inizia dall'indice massimo contenuto nella ListBox e scala di 1 fino a zero
    Me.Controls("ListBox" & B).RemoveItem z
    Next
    End If
    Next
    End Sub

Se poi vorremo usare una istruzione per copiare il contenuto (i dati) della ListBox1 in un'altro foglio, in modo da formare delle nuove tabelle con questi dati, potrete usare questa sotto; ho inserito tramite tre InputBox, la richiesta per indicare su quale foglio copiare i dati (e andrà indicato il nome del foglio, ad esempio Foglio3), poi a seguire andrà indicato da quale riga iniziare a incollare, infine da quale numero di colonna (numero, non lettera) iniziare a incollare. Questa è una aggiunta e queste istruzioni non sono inserite nel file allegato, e chi vorrà potrà copiarle ed inserirle in un altro CommandButton (evento Click) posto sulla UserForm :

  • Private Sub CommandButton5_Click() 'pulsante aggiunto alla UserForm
    Dim Ri As Integer 'variabili con le quali reperiamo dalle InputBox i tre dati descritti nella richiesta
    Dim Co As Integer
    Dim Fo As String 'essendo Fo dichiarata come stringa, non è necessario scrivere il nome foglio tra doppi apici
    Fo = InputBox("SCRIVI IL NOME DEL FOGLIO DESTINAZIONE")
    Ri = InputBox("SCRIVI DA QUALE RIGA INIZIARE A COPIARE")
    Co = InputBox("SCRIVI DA QUALE NUMERO COLONNA INIZIARE A COPIARE")
    Qr = ListBox1.ListCount 'contiamo le righe occupate nella ListBox1
    Cr = ListBox1.ColumnCount 'contiamo le colonne occupate nella ListBox1, e quindi iniziamo un doppio ciclo che 'copierà sul foglio prescelto con "Fo" i dati delle righe e delle colonne nella ListBox1
    For R = 0 To Qr - 1
    For C = 0 To Cr - 1
    Sheets(Fo).Cells(R + Ri, C + Co) = ListBox1.List(R, C)
    Next: Next
    End Sub

File allegato consultabile e scaricabile:

nome file dimensione n.download
ListBoxcolonne.zip 17  Kb

 

Buon lavoro

prelevato sul sito www.ennius.altervista.org