combobox in userform con valori ordinati e senza duplicati

Question

Buonasera,

Avrei alcune cose da chiedervi in merito alle combobox contenute all'interno di una userform.

Andiamo in ordine; supponiamo io abbia un file excel contenente una sheet chiamata FRUTTA e nella quale ci siano questi valori disposti in tabella:

DESCRIZIONE	RIVENDITORE	QUANTITATIVO IN KG	REGIONE PROVENIENZA
MELE	RIVENDITORE1	10	LOMBARDIA
PERE	RIVENDITORE1	5	CAMPANIA
MELE	RIVENDITORE3	10	LOMBARDIA
ARANCE	RIVENDITORE2	3	SICILIA
CILIEGIE	RIVENDITORE3	20	PUGLIA
PERE	RIVENDITORE2	8	LAZIO
FRAGOLE	RIVENDITORE2	3	CAMPANIA
MELE	RIVENDITORE1	5	LOMBARDIA
ARANCE	RIVENDITORE3	5	SICILIA

Supponiamo, adesso, che io voglia realizzare una userform contenente 4 combobox ciascuna collegata in rowsource alle 4 colonne di cui sopra. La ComboBox1 collegata alla colonna Descrizione, la ComboBox2 alla colonna Rivenditore, la ComboBox3 alla colonna del quantitativo, e la ComboBox4 alla colonna della regione di provenienza. Clikkando, ad esempio, sul menu a tendina della ComboBox1 mi apparirà lo stesso elenco di cui sopra: nello stesso ordine e con varie ripetizioni; la voce "MELE", ad esempio, sarà riportata 3 volte e quella "PERE" due volte. Io vorrei, invece, che ciascuna di quelle voci (nella combobox1) fosse riportata una sola volta anche là dove, in quella colonna, fosse presente più e più volte. Vorrei anche, se possibile, che quei valori, nella ComboBox1, fossero ordinati in modo crescente: dunque prima le ARANCE, poi le CILIEGIE, poi le FRAGOLE e via discorrendo (mentre nella colonna DESCRIZIONE dovrebbero restare nello stesso ordine in cui si trovano).

Supponiamo, adesso, che in quella tabella io metta un filtro sulla riga delle intestazioni. Fatto ciò, dalla colonna DESCRIZIONE filtro "PERE". Le righe filtrate saranno due: nella colonna RIVENDITORE avrò, pertanto, RIVENDITORE1 in una riga e RIVENDITORE2 in un'altra. Nella colonna del quantitativo avrò 5 in una riga e 8 in un'altra e nella colonna della regione di provenienza avrò CAMPANIA in una riga e LAZIO in un'altra.

A questo punto, dopo aver avviato la userform, chiedo se sia possibile fare quest'altra cosa.  Vorrei che nel menu della ComboBox1 (quella collegata alla colonna DESCRIZIONE) appaia solo la voce "PERE" (cioè quella precedentemente filtrata nella prima colonna della tabella); in quello della ComboBox2, colonna Rivenditore, dovrebbero essere riportate solo RIVENDITORE1 e RIVENDITORE2; nella ComboBox3, quella del quantitativo, 5 e 8, e nella ComboBox4, quella della regione di provenienza, CAMPANIA e LAZIO. Vorrei, in sostanza, che dopo aver applicato il filtro alla mia tabella, le combobox della userform si comportassero in maniera analoga al menu a tendina del filtro (cioè visualizzando solo i corrispettivi del filtro di cui sopra).

Spero di essere riuscito a farmi capire. Uso Excel 2007.

Mille grazie in anticipo a chi saprà aiutarmi

Answer 1

Ciao Luca,

Avrei alcune cose da chiedervi in merito alle combobox contenute all'interno di una userform.

Andiamo in ordine; supponiamo io abbia un file excel contenente una sheet chiamata FRUTTA e nella quale ci siano questi valori disposti in tabella:

DESCRIZIONE	RIVENDITORE	QUANTITATIVO IN KG	REGIONE PROVENIENZA
MELE	RIVENDITORE1	10	LOMBARDIA
PERE	RIVENDITORE1	5	CAMPANIA
MELE	RIVENDITORE3	10	LOMBARDIA
ARANCE	RIVENDITORE2	3	SICILIA
CILIEGIE	RIVENDITORE3	20	PUGLIA
PERE	RIVENDITORE2	8	LAZIO
FRAGOLE	RIVENDITORE2	3	CAMPANIA
MELE	RIVENDITORE1	5	LOMBARDIA
ARANCE	RIVENDITORE3	5	SICILIA

Supponiamo, adesso, che io voglia realizzare una userform contenente 4 combobox ciascuna collegata in rowsource alle 4 colonne di cui sopra. La ComboBox1 collegata alla colonna Descrizione, la ComboBox2 alla colonna Rivenditore, la ComboBox3 alla colonna del quantitativo, e la ComboBox4 alla colonna della regione di provenienza. Clikkando, ad esempio, sul menu a tendina della ComboBox1 mi apparirà lo stesso elenco di cui sopra: nello stesso ordine e con varie ripetizioni; la voce "MELE", ad esempio, sarà riportata 3 volte e quella "PERE" due volte. Io vorrei, invece, che ciascuna di quelle voci (nella combobox1) fosse riportata una sola volta anche là dove, in quella colonna, fosse presente più e più volte.

OK!

Vorrei anche, se possibile, che quei valori, nella ComboBox1, fossero ordinati in modo crescente: dunque prima le ARANCE, poi le CILIEGIE, poi le FRAGOLE e via discorrendo (mentre nella colonna DESCRIZIONE dovrebbero restare nello stesso ordine in cui si trovano).

Supponiamo, adesso, che in quella tabella io metta un filtro sulla riga delle intestazioni. Fatto ciò, dalla colonna DESCRIZIONE filtro "PERE". Le righe filtrate saranno due: nella colonna RIVENDITORE avrò, pertanto, RIVENDITORE1 in una riga e RIVENDITORE2 in un'altra. Nella colonna del quantitativo avrò 5 in una riga e 8 in un'altra e nella colonna della regione di provenienza avrò CAMPANIA in una riga e LAZIO in un'altra.

OK!

A questo punto, dopo aver avviato la userform, chiedo se sia possibile fare quest'altra cosa.  Vorrei che nel menu della ComboBox1 (quella collegata alla colonna DESCRIZIONE) appaia solo la voce "PERE" (cioè quella precedentemente filtrata nella prima colonna della tabella); in quello della ComboBox2, colonna Rivenditore, dovrebbero essere riportate solo RIVENDITORE1 e RIVENDITORE2; nella ComboBox3, quella del quantitativo, 5 e 8, e nella ComboBox4, quella della regione di provenienza, CAMPANIA e LAZIO. Vorrei, in sostanza, che dopo aver applicato il filtro alla mia tabella, le combobox della userform si comportassero in maniera analoga al menu a tendina del filtro (cioè visualizzando solo i corrispettivi del filtro di cui sopra).

Comunque, se fosse possibile che due voci fossero selezionate come criteri nella prima colonna dell'filtro automatico - ad esempio PERE e CILIEGIE - cosa ti aspettirebbe di vedere nel secondo ComboBox e in che ordine?

Sebbene tu abbia spiegato alcuni dei risultati desiderati, non hai spiegato lo scopo dell'userform o quello che ti proponi di raggiungere, entrambe di quali considerazioni potrebbero avere un effetto materiale sulla formulazione del codice richiesto.

===

Regards,

Norman

Answer 2

Ciao Luca, 

Una cara persona ha scritto per me questo codice:

Private Sub UserForm_Initialize()

    Dim a As Object

    Dim t As ListObject

    Dim rr As Range, r As Range

    Dim s As String

    

    Set a = CreateObject("system.collections.arraylist")

    Set t = Worksheets("Sheet1").ListObjects("Table1")

    On Error Resume Next

    Set rr = t.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

    If Not rr Is Nothing Then

        For Each r In rr

            s = CStr(r.Value)

            If Not a.contains(s) Then

                a.Add s

            End If

        Next

        a.Sort

        ComboBox1.List = a.toarray

    End If

    

End Sub

Adattato alla mia UserForm funziona perfettamente. Problema risolto!

Ringrazio, cmq, Norman per il gentile interessamento

Scusami se esprimo qualche sorpresa!

A mio modesto parere, il codice che hai pubblicato non soddisfa neanche lontanamente le complesse esigenze precisate nella tua risposta precedente.

La mia interpretazione di questo codice suggerirebbe che si limita al caricamento di una sola ComboBox con un elenco ordinato dei valori di una sola colonna della tua tabella. Quindi, se come hai indicato, questa semplice procedura, risolva i tuoi problemi, debbo concludere che io abbia letto molto male la tua richiesta.

Tuttavia, credo che l'importante sia che tu abbia risolto il problema e sono ben felice che tu sia riuscito a farlo.

Alla prossima.

===

Regards,

Norman

Caro Norman,

Ti sorprenderà ma quel codice, adattato alle mie esigenze, rilsolve i miei problemi. Già nel mio precedente post avevo scritto "Adattato alla mia UserForm funziona perfettamente". Affinché venga fatto tutto ciò che avevo richiesto mi è stato sufficiente fare così:

1. Nell'evento Initialize della mia userform ho riportato quel codice tante volte quante sono le combobox contenute nell'user (ovviamente cambiando il nome delle variabili):

' ---------------------------------------------

' PERMETTE DI VEDERE, NELLA COMBOBOX1, I DATI UNIVOCI E FILTRATI DELLA COLONNA A DELLA TABELLA4 (QUELLA DEGL'ARTICOLI)

    Dim a1 As Object

    Dim t1 As ListObject

    Dim rr1 As Range, r1 As Range

    Dim s1 As String

    Set a1 = CreateObject("system.collections.arraylist")

    Set t1 = Worksheets("ARTICOLI").ListObjects("TABELLA4")

    On Error Resume Next

    Set rr1 = t1.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

    If Not rr1 Is Nothing Then

        For Each r1 In rr1

            s1 = CStr(r1.Value)

            If Not a1.contains(s1) Then

                a1.Add s1

            End If

        Next

        a1.Sort

        ComboBox1.List = a1.toarray

    End If

'------------------------------------------------

' PERMETTE DI VEDERE, NELLA COMBOBOX2, I DATI UNIVOCI E FILTRATI DELLA COLONNA C DELLA TABELLA4 (QUELLA DEGL'ARTICOLI)

    Dim a2 As Object

    Dim t2 As ListObject

    Dim rr2 As Range, r2 As Range

    Dim s2 As String

    Set a2 = CreateObject("system.collections.arraylist")

    Set t2 = Worksheets("ARTICOLI").ListObjects("TABELLA4")

    On Error Resume Next

    Set rr2 = t2.DataBodyRange.Columns(3).SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

    If Not rr2 Is Nothing Then

        For Each r2 In rr2

            s2 = CStr(r2.Value)

            If Not a2.contains(s2) Then

                a2.Add s2

            End If

        Next

        a2.Sort

        ComboBox2.List = a2.toarray

    End If

'------------------------------------------------

...e via discorrendo.

Dopo aver fatto ciò, nella UserForm, accanto a ciascuna Combo, ho inserito uno strumento image (raffigurante un filtro) da usare come pulsante. Nell'evento click di ciascuna di quelle image ho messo questo codice (che varia di volta in volta):

Private Sub Image17_Click()

' METTE UN FILTRO ALLA COLONNA 23 DELLA TABELLA4

Dim tuocrit As String

tuocrit = ComboBox9.Value

ActiveSheet.ListObjects("Tabella4").Range.AutoFilter Field:=23, _

               Criteria1:=tuocrit

'------------------------------------------------

' PERMETTE DI VEDERE, ANCHE DOPO AVER FILTRATO, DATI UNIVOCI E ORDINATI COME IN UN Menù DEL FILTRO AUTOMATICO

Call UserForm_Initialize

'------------------------------------------------

End Sub

Come puoi vedere, usando l'istruzione Call richiamo l'evento Initialize in modo tale che, dopo aver applicato un filtro ad una tale colonna, si aggiorni, in automatico, il contenuto anche delle altre combo.

Infine, accanto a ciascuna combo, ho messo un'altra image (raffigurante un filtro col segno meno) da usare come pulsante per togliere il filtro. All'interno dell'evento click di ciascuna di quelle image, ho messo qualcosa del genere:

Private Sub Image16_Click()

' TOGLIE IL FILTRO DALLA COLONNA 23 (MARCA) DELLA TABELLA DEGLI ARTICOLI (TABELLA4)

    ActiveSheet.ListObjects("Tabella4").Range.AutoFilter Field:=23

'------------------------------------------------

' PERMETTE DI VEDERE, ANCHE DOPO AVER FILTRATO, DATI UNIVOCI E ORDINATI COME IN UN Menù DEL FILTRO AUTOMATICO

Call UserForm_Initialize

'------------------------------------------------

End Sub

La procedura è simile a quella dell'applicazione del filtro sopra descritta e il gioco è fatto.

Forse, in precedenza, non mi ero spiegato bene ma avevo già scritto che sapevo, di mio, come mettere e togliere dei filtri da UserForm; così come sapevo come fare ad aggiornare, di volta, in volta, le varie combo. Ciò che mi mancava era vedere nella combobox solo i dati filtrati, senza doppioni e ordinati progressivamente. Quel codice fa questo e, una volta adattato a ciascuna combo, il problema è stato risolto.

Ancora grazie dell'interessamento. Al solito, sei sempre molto gentile e disponibile.

Ciao

Answer 3

Ciao Luca, 

Una cara persona ha scritto per me questo codice:

Private Sub UserForm_Initialize()

    Dim a As Object

    Dim t As ListObject

    Dim rr As Range, r As Range

    Dim s As String

    

    Set a = CreateObject("system.collections.arraylist")

    Set t = Worksheets("Sheet1").ListObjects("Table1")

    On Error Resume Next

    Set rr = t.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

    If Not rr Is Nothing Then

        For Each r In rr

            s = CStr(r.Value)

            If Not a.contains(s) Then

                a.Add s

            End If

        Next

        a.Sort

        ComboBox1.List = a.toarray

    End If

    

End Sub

Adattato alla mia UserForm funziona perfettamente. Problema risolto!

Ringrazio, cmq, Norman per il gentile interessamento

Scusami se esprimo qualche sorpresa!

A mio modesto parere, il codice che hai pubblicato non soddisfa neanche lontanamente le complesse esigenze precisate nella tua risposta precedente.

La mia interpretazione di questo codice suggerirebbe che si limita al caricamento di una sola ComboBox con un elenco ordinato dei valori di una sola colonna della tua tabella. Quindi, se come hai indicato, questa semplice procedura, risolva i tuoi problemi, debbo concludere che io abbia letto molto male la tua richiesta.

Tuttavia, credo che l'importante sia che tu abbia risolto il problema e sono ben felice che tu sia riuscito a farlo.

Alla prossima.

===

Regards,

Norman

Answer 4

Una cara persona ha scritto per me questo codice:

Private Sub UserForm_Initialize()

    Dim a As Object

    Dim t As ListObject

    Dim rr As Range, r As Range

    Dim s As String

    Set a = CreateObject("system.collections.arraylist")

    Set t = Worksheets("Sheet1").ListObjects("Table1")

    On Error Resume Next

    Set rr = t.DataBodyRange.Columns(1).SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

    If Not rr Is Nothing Then

        For Each r In rr

            s = CStr(r.Value)

            If Not a.contains(s) Then

                a.Add s

            End If

        Next

        a.Sort

        ComboBox1.List = a.toarray

    End If

End Sub

Adattato alla mia UserForm funziona perfettamente. Problema risolto!

Ringrazio, cmq, Norman per il gentile interessamento

Answer 5

Ciao Norman,

Innanzitutto ti ringrazio di aver risposto.

Nell'eventualità da te proposta (filtro su PERE e CILIEGIE), mi aspetterei che nella ComboBox2 (quella collegata alla colonna RIVENDITORE) appaiano i valori corrispondenti a PERE e CILIEGIE come da esempio proposto (cioè RIVENDITORE1, RIVENDITORE2, e RIVENDITORE3). Analogo discorso per le restanti due colonne.

Hai ragione, non ho spiegato il fine ultimo della UserForm. Chiedo scusa.

La tabella sopra proposta è solo un esempio molto semplice (usando la prima tipologia di prodotti venutami a mente) di una mia tabella prodotti molto più complessa e lunga (contiene circa 40 colonne e oltre mille righe).

La UserForm proposta mi servirebbe, innanzitutto, ad apllicare, attraverso di essa, il filtro alla tabella. Questo non lo avevo specificato perché è un qualcosa che so già fare. In secondo luogo, collegando (dentro una userform) una ComboBox a ciascuna colonna, ottenendo quel che ho già specificato volere per ciascuna combo, miglioro la visibilità dei miei dati in tabella. Alla stato attuale, dopo aver applicato il filtro, io devo spostarmi da un capo all'altro della sheet (circa 40 colonne) per vedere ciò che è scaturito dal filtro. Visto, poi, che in fondo alla tabella ci sono anche alcuni totali (ottenuti con la funzione SUBTOTALE), per visualizzarli devo spostarmi in fondo alla tabella.

Io, invece, vorrei, sotto certi aspetti, mettere tutto (totali compresi) in una userform. Metto il filtro alla colonna1 e nelle varie combo, collegate a ciascuna colonna, vedo il contenuto filtrato. Infine, nella UserForm metto anche delle TextBox collegate alle celle dei totali (cosa che so fare) visualizzando, così,  i totali scaturiti dal filtraggio.

Comunque, visto che (prima del tuo post) non avevo ricevuto ancora risposta, mi sono dato un pò da fare. Leggendo qua e là sui siti in lingua inglese, ho messo in piedi queste righe di codice da inseire nell'evento Initialize della mia UserForm.

    Dim Lrow As Long, test As New Collection

    Dim Value As Variant, temp As Range

    Dim i As Single

    On Error Resume Next

    Set temp = Worksheets("FRUTTA").ListObjects("TABELLA1").ListColumns(1).Range

    For i = 2 To temp.Cells.Rows.Count

        If Len(temp.Cells(i)) > 0 And Not temp.Cells(i).EntireRow.Hidden Then

            test.Add temp.Cells(i), CStr(temp.Cells(i))

        End If

    Next i

    UserForm1.ComboBox1 = Clear

    For Each Value In test

         UserForm1.ComboBox1.AddItem Value

    Next Value

    Set test = Nothing

Questo codice mi consente di visualizzare, nella ComboBox1, i soli dati filtrati nella colonna DESCRIZIONE senza ripetizioni. Apllicandolo anche alle altre ComboBox ottengo il medesimo risultato. Non sono ancora riuscito, però, a far sì che quei dati, nella combo, appaiano ordinati in modo progressivo.

Spero riuscirai a darmi una mano. Ad ogni modo, grazie.