ComboBox : ordinare la lista dei dati.    (21/06/03)

Una delle domande che spesso mi viene rivolta è : come eliminare gli spazi vuoti dall'elenco di una ComboBox (delle ActiveX); spazi vuoti rappresentati dalle celle senza dati presenti nel range assegnato alla proprietà ListFillRange ,se la combo è inserita sul foglio di lavoro, o dalla proprietà RowSource se la combo è inserita in una UserForm. Il discorso vale comunque anche per le ListBox.

Vedremo anche come far caricare una sola volta i dati se questi sono doppi. Ma diamo un occhiata ad un esempio di un elenco preparato ad hoc:

Come si nota, i dati nella colonna B (da B5 in giù) non sono in righe contigue, e la ComboBox che "pesca" la lista (con la proprietà ListFillRange) in questo range, presenta analogamente un elenco con spazi vuoti. La cosa di per se non rappresenta un errore, ma può dare fastidio dover scorrere un menù più lungo, alla ricerca del valore voluto. Inoltre i dati nel menù, rispecchiando fedelmente l'origine dati, non sono in ordine alfabetico, cosa che ci farebbe piacere specie con elenchi lunghi dove risulterebbe più veloce spostarsi ad una determinata lettera iniziale. Qualcuno potrebbe obiettare che basterebbe creare un ordinamento sulla tabella di origine, per vederli in ordine anche nella combobox, ma esistono molti casi in cui questo non è possibile, specie con elenchi impostati su chiavi che non sono le stesse dei dati richiamati dalla combobox, o ancora casi in cui l'inserimento di dati nella tabella avviene in progressione, senza poter determinare un ordine di inserimento basato sui dati richiamati nella combobox. Insomma, qualunque sia il motivo, andiamo avanti.

Un modo abbastanza veloce e semplice per la nostra esigenza può essere questo:

  • Copiare in una altra colonna del foglio, in una colonna fuori vista (io userò la colonna H nell'esempio), i dati contenuti nella colonna B.

  • Applicare un ordinamento ascendente su questa colonna (la H) per ordinare alfabeticamente i nomi. Con questo passo le celle vuote vengono automaticamente accodate all'elenco che si ordinerà.

  • Assegnare questa colonna (H) come ListFillRange della ComboBox, sfruttando End per reperire l'ultima cella occupata. In questo modo il nostro elenco sarà depurato dagli spazi vuoti, e con i nomi in ordine alfabetico.

  • Queste sono le immagini della ComboBox "epurata" e ordinata e della nuova lista che si crea nella colonna H

Ed ora vediamo come procedere, e poi le istruzioni, che ho posizionato nell'evento Click della ComboBox1. In verde le spiegazioni.

  • iniziamo ad assegnare, in "modalità progettazione" (che si attiva appena inserita la ComboBox sul foglio), nella finestra delle sue proprietà, oltre alla LinkedCell, anche un range di celle alla proprietà ListFillange. Operazione NECESSARIA per inizializzare un elenco nella stessa combo, altrimenti sarebbe vuota e non si genererebbe nè l'evento Click, nè l'evento Change necessari ad attivare le istruzioni, oppure potremmo sfruttare l'evento Workbook_Open per "caricare" la lista della combo box, con un'istruzione tipo:

Private Sub Workbook_Open()
Worksheets("Foglio1").ComboBox1.ListFillRange = "B5:B100"

'e già che ci siamo, impostiamo anche la LinkedCell

Worksheets("Foglio1").ComboBox1.LinkedCell = "B2" 'o la cella che vorrete
End Sub

  • Non ha importanza il range che viene caricato in quanto al primo click nella lista della combobox otterremo l'attivazione della macro e dell'ordinamento.

Private Sub ComboBox1_Click()

'sotto. "puliamo" la ListFillRange della Combobox
ComboBox1.ListFillRange = ""

'sotto : puliamo la colonna H predisponendola alla copia del nuovo elenco
ActiveSheet.Range("H:H").ClearContents

'sotto : impostiamo con "zona" il range che dovrà essere copiato; poichè non è possibile 'usare End per determinare la fine elenco, dovremo assegnare un numero di celle che 'preveda successivi inserimenti di dati
Set zona = Range("B5:B100")
zona.Select  'si seleziona il range con i dati e le celle vuote (zona)
zona.Copy Range("H5")  'e la si copia nella colonna H a partire da H5

'sotto : poichè avremo una copia che contiene celle vuote anche in H, non potremo usare 'End per gli stessi motivi, assegneremo noi un range che copra quello di origine dati, e lo 'selezioniamo
Range("H5:H100").Select

'sotto: ora si esegue l'ordinamento sulla selezione (H)
Selection.Sort Key1:=Range("H5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'sotto: ora che le celle vuote sono tutte finite a fine elenco, possiamo usare End per 'reperire l'ultima cella con i dati, ed assegniamo a due variabili, gli indirizzi della cella 'iniziale ( x ) e finale ( y )
x = Cells(5, 8).Address
y = Cells(5, 8).End(xlDown).Address

'sotto : ora con i riferimenti esatti alle celle, carichiamo la ListFillRange della combobox
ComboBox1.ListFillRange = "" & x & ":" & y & ""
Range("B2").Select  ' poi selezioniamo una cella per deselezionare l'area in H
End Sub

Se invece vorremo una lista che non preveda le voci doppie, dovremo inserire nella precedente routine, altre istruzioni per l'eliminazione dei doppioni. (routine peraltro già presente in questa sezione, articolo "Eliminare dati doppioni". Vediamo sotto come integrare le due serie di istruzioni. Non ripeto le spiegazioni:

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = ""
ActiveSheet.Range("H:H").ClearContents
Set zona = Range("B5:B100")
zona.Select
zona.Copy Range("H5")
Range("H5:H100").Select
Selection.Sort Key1:=Range("H5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'sotto, inizia il controllo dei doppioni, nel caso venga trovato un dato doppio, viene 'cancellata la CELLA
Set currentCell = Worksheets("Foglio1").Range("H5")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 0)
If nextCell.Value = currentCell.Value Then
currentCell.Cells.Delete
End If
Set currentCell = nextCell
Loop
'poi si continua come sopra
x = Cells(5, 8).Address
y = Cells(5, 8).End(xlDown).Address
ComboBox1.ListFillRange = "" & x & ":" & y & ""
Range("B2").Select
End Sub

 

 

Buon lavoro.

prelevato sul sito http://ennius.interfree.it