Excel VB Popolare una ListBox Filtrando i dati per lettere alfabetiche

Question

Buon Giorno a tutti

Come faccio a Filtrare in una ListBox  un  elenco di nominativi presenti in una Colonna ( "A")  di una  Tabella di un Foglio Excel  , immettendo una lettera in una TextBox .

Esempio 

Inserisco nella TextBox   la lettera   "C"  e  Vorrei .... che nella ListBox venga caricato  SOLO  l'elenco dei Nominativi che Iniziano per  "C"

 ..... ho imparato a popolare correttamente le ListBox ... ma non conosco il modo corretto per dare l'istruzione VB  per filtrare  i dati di un Range di celle  .................           Grazie a chi mi da' un aiuto       Claudio P

Answer 1

Ciai Claudio,

.... guarda che la mia non era un'osservazione negativa ... anzi non c'e' modo per ringraziarti a sufficenza per la tua disponibilita'  e competenza .... semplicemente , considerando che non vorrei solo .... copiare ... ma anche capire , questo e' Mooooolto al di sopra delle mie attuali competenze , ergo per comprendere ci dovro' studiare sopra molto.

Tranquillo - non ho afatto preso  i tuoi commeni in modo negativo! Proprio perchè mi pareva una osservazione del tutto ragionevole, ho provato a darti una spiegazione dettagliata.

Detto questo , ho scaricato il tuo file di prova , volevo vedere come si comporta , per poi adattarlo alla mia userform

Lanciato , messaggio  Errore Run Time 2146232576 (80131760)    Errore di Automazione

Debug  evidenzia in giallo     UserForm1.Show  vbModeless

.... cosa devo fare ...

Questo è un errore che può verificarsi improvvisamente; spesso riflette un errore interno che non ha alcun nesso diretto o ovvio con il codice evidenziato. Ad ogni modo, credo che il codice evidenziato sia ineccepibile. Ho appena scaricato il file ed avviato la Userform senza alcun problema. Pertanto, prova a chiudere Excel e poi riaprirlo, magari dopo aver ricaricato il file.

io intanto aggiungero' nella mia UserForm una TextBox (la ListBox c'e' gia' ) i due  CommandButton      e copiato incollato il tuo codice , lo adattero'  .

Vediamo se gira  

Facci sapere i tuoi progressi.

===

Regards,

Norman

Answer 2

Ciao Claudio,

Per un codice breve per filtrare i dati nella colonna A della tua tabella, potresti provare la seguente versione:

'=========>>

Option Explicit

Dim Rng As Range

'--------->>

Private Sub UserForm_Initialize()

    Set Rng = ActiveSheet.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

       Me.lbDatiFiltrati.List = Rng.Value

End Sub

'--------->>

Private Sub cbFiltra_Click()

Dim rCell As Range

Me.lbDatiFiltrati.Clear

    For Each rCell In Rng.Cells

        If Left(rCell.Value, 1) = Me.tbCriterioFiltraggio Then

        Me.lbDatiFiltrati.AddItem rCell.Value

        End If

    Next rCell

End Sub

'<<=========

Anche se questo codice è molto più breve rispetto al codice che ho postato in precedenza, non credo che sia lontanamente paragonabile in termini di facilità d'uso, efficienza o funzionalità. 

Per quanto riguarda la facilità d'uso, credo che vedrai subito una differenza fin dal momento che avvii la Userform.

Per quanto riguarda l'efficienza del codice, considera, tra l'altro, che se la tabella comprendesse (diciamo) diecimila righe, sarebbe necessario interrogare il foglio 10 mila volte per ogni operazione di filtraggio. Con il codice originale, solo una singola interrogazione del foglio sarebbe necessario, indipendentemente dal numero di operazioni di filtraggio effettuate dall'utente. Per una tabella di dati abbastanza piccola, i risultati della differenza di prestazioni possono essere sostanzialmente impercettibili per l'utente. Tuttavia, nel caso di un grande insieme di dati, o una cartella di lavoro che richiede un utilizzo intenso delle risorse disponibili, la differenza potrebbe essere altamente significativa, e perfino critica.

Tornando alla disparità di funzionalità, nota che:

• Il codice più corto richiede un'osservanza absoluta di lettere maiuscole e minuscole;
• A differenza del codice originale, il codice breve non ordina né i dati di uscita o di ingresso.
• Il codice corto può filtrare i dati unicamente sulla prima lettera mentre il codice originale eseguirà un'operazione di filtraggio basato su un numero qualsiasi di lettere.

Per vedere il codice in azione, scarica il file ClaudioAbbreviato20161023.xlsm a:

https://www.dropbox.com/s/i7xb534ktwer4ux/ClaudioAbbreviato20161023.xlsm?dl=0

===

Regards,

Norman

Answer 3

Ciao Claudio,

Come faccio a Filtrare in una ListBox  un  elenco di nominativi presenti in una Colonna ( "A")  di una  Tabella di un Foglio Excel  , immettendo una lettera in una TextBox .

Esempio  

Inserisco nella TextBox   la lettera   "C"  e  Vorrei .... che nella ListBox venga caricato  SOLO  l'elenco dei Nominativi che Iniziano per  "C"

 ..... ho imparato a popolare correttamente le ListBox ... ma non conosco il modo corretto per dare l'istruzione VB  per filtrare  i dati di un Range di celle  

In un modulo standard, incolla il seguente codice:

'=========>>

Option Explicit

'--------->>

Public Sub Tester()

    UserForm1.Show vbModeless

End Sub

'--------->>

Public Function SortedList(V As Variant)

    Dim oSortedList As Object

    Dim arrOut() As Variant

    Dim sStr As String

    Dim i As Long

    Set oSortedList = CreateObject("System.Collections.Sortedlist")

    With oSortedList

        For i = LBound(V) To UBound(V)

            sStr = V(i)    ', 1)

            If Not sStr = vbNullString Then

                If Not .ContainsKey(sStr) Then

                    .Add Key:=sStr, Value:=i

                End If

            End If

        Next i

        ReDim arrOut(1 To .Count)

        For i = 0 To .Count - 1

            arrOut(i + 1) = .GetKey(i)

        Next i

    End With

    SortedList = arrOut

End Function

'--------->>

Public Function LastRow(SH As Worksheet, _

                        Optional Rng As Range, _

                        Optional minRow As Long = 1)

    If Rng Is Nothing Then

        Set Rng = SH.Cells

    End If

    On Error Resume Next

    LastRow = Rng.Find(What:="*", _

                       after:=Rng.Cells(1), _

                       Lookat:=xlPart, _

                       LookIn:=xlFormulas, _

                       SearchOrder:=xlByRows, _

                       SearchDirection:=xlPrevious, _

                       MatchCase:=False).Row

    On Error GoTo 0

    If LastRow < minRow Then

        LastRow = minRow

    End If

End Function

'<<=========

Crea una Userform con i seguenti controlli:

• Una ListBox  (lbDatiFiltrati)
• Una TextBox (tbCriterioFiltraggio)
• Un CommandButton (cbFiltra)
• Un secondo CommandButton (cbEsce)
• Un Label (lblCriterioFiltro)

Nel Modulo di codice della Userform, incolla il seguente codice:

'=========>>

Option Explicit

Option Compare Text

Dim WB As Workbook

Dim srcSH As Worksheet

Dim RngTabella As Range

Dim arrIn As Variant

'--------->>

Private Sub UserForm_Initialize()

    Dim i As Long, j As Long, k As Long

    Dim LRow As Long

    Const sFoglio As String = "Foglio1"                   '<=== Modifica

    Set WB = ThisWorkbook

    Set srcSH = WB.Sheets(sFoglio)

    With srcSH

        LRow = LastRow(srcSH, .Columns("A:A"))

        Set RngTabella = .Range("A2:A" & LRow)

    End With

    arrIn = RngTabella.Value

    With Me

        .BackColor = &HC0FFFF

        .Caption = "Filtra Tabella"

        With .cbEsce

            .Caption = "Esce"

            .ForeColor = &HFF&

        End With

        With .cbFiltra

            .Caption = "Filtra Dati!"

            .ForeColor = &HFF&

            .TabIndex = 2

        End With

        With .lbDatiFiltrati

            .MatchEntry = fmMatchEntryComplete

            .Font = "Tahoma"

            With .Font

                .Size = 10

            End With

            .List = SortedList(Application.Transpose(arrIn))

        End With

        With .lblCriterioFiltro

            .Caption = "Lettere Iniziali del Filtro"

            .BackColor = Me.BackColor

            .ForeColor = &HFF0000

            .Font = "Tahoma"

            With .Font

                .Bold = True

                .Size = 7

            End With

        End With

        With .tbCriterioFiltraggio

            With .Font

                .Bold = True

                .Size = 10

            End With

            .ForeColor = &HFF0000

            .TabIndex = 1

            .SetFocus

        End With

    End With

End Sub

'--------->>

Private Sub cbFiltra_Click()

    Dim arrFiltrati() As Variant

    Dim sCriterio As String

    Dim iLen As Long, i As Long, j As Long

    With Me

        sCriterio = .tbCriterioFiltraggio.Text

        If sCriterio = vbNullString Then

            Call MsgBox( _

                 Prompt:="Non hai imesso alcun criterio di filtaggio" _

                       & vbNewLine & vbNewLine _

                       & "I dati non sono filtrati!", _

                 Buttons:=vbInformation, _

                 Title:="REPORT")

            .lbDatiFiltrati.List = arrIn

            Me.tbCriterioFiltraggio.SetFocus

            Exit Sub

        Else

            iLen = Len(sCriterio)

        End If

        For i = 1 To UBound(arrIn)

            If Left(arrIn(i, 1), iLen) = sCriterio Then

                j = j + 1

                ReDim Preserve arrFiltrati(1 To j)

                arrFiltrati(j) = (arrIn(i, 1))

            End If

        Next i

        With .lbDatiFiltrati

            If CBool(j) Then

                .List = SortedList(arrFiltrati)

            Else

                .Clear

                Call MsgBox(Prompt:="Dati non trovati - controlla il criterio!", _

                            Buttons:=vbInformation, _

                            Title:="REPORT")

                Me.tbCriterioFiltraggio.SetFocus

            End If

        End With

    End With

End Sub

'--------->>

Private Sub cbEsce_Click()

    Unload Me

End Sub

'<<=========

Nota i seguenti punt:

• All'avvio della Userform. i dati della prima colonna della tabella verranno caricati nel controllo ListBox in modo ordinato;
• I dati filtati saranno sempre ordinati;
• Nel controllo ListBox, si può selezionare dati digitando il primo carattere
• Si può raffinare la ricerca ulteriormente digitando dei caratteri successivi
• Il filtraggio non fa distinzione tra maiuscole e minuscole (case insensitive)
• I dati della tabella possono essere filtrati utilizzando il primo carattere come il criterio di filtro o il filtraggio  può essere reso più preciso stipulando più caratteri.
• Sul Foglio1 si trova un pulsante per avviare la Userform:

Potresti scaricare il mio file di prova Claudio20161021.xlsm a:

https://www.dropbox.com/s/ahfzy0zfoj63ig8/Claudio20161021.xlsm?dl=0

===

Regards,

Norman

Answer 4

Ciao Claudio,

Ci  dovro'  studiare  sopra per mesi ... , non pensavo che  per dare l'istruzione filtra per ......  servissero tutte queste righe di codice ,  .....

Posso fornire codice molto più corto se vuoi che il codice sia meno verboso. Tuttavia, deve decidere se il codice dovrebbe essere efficente e veloce oppure breve e meno efficiente. Inoltre, potrei facilmente abbreviare il codice se dovessi ridurre la sua funzionalità: ad esempio potrei cancellare il codice per ordinare i dati nel controllo ListBox  e potrei lasciare tutti i settagi dei controlli a te da fare a mano.

A questo proposito, in un altro forum Italiano per Excel che frequento, ho recentemente postato una soluzione come alternativa ad una soluzione che era già stato postato da un contributore dotato. È emerso che, nonostante il fatto che  il mio codice fosse molto più lungo che il suo, esso era più di 100 volte più veloce. In somma, la lunghezza di un dato codice non è un fattore determinante della sua efficienza e qualsiasi tentativo di limitare intenzionalmente il codice potrebbe avere un effetto altamente negativo sulla sua efficienza. Tuttavia, il fatto che il codice sia lungo non significa, per sé, che sia efficiente o consigliabile. A mio modesto parere, le caratteristiche più importanti di ogni buon codice sono la sua funzionalità e la competenza con cui è stato scritto. Ti posso assicurare che ci sia una vera e propria pletora di codice disponibile sull'internet che è stato scritto molto male e, tra questo codice, c`è molto che io troverei schifoso o addirittura vergognoso!

===

Regards,

Norman

Answer 5

Ciao  Norman

............ Come  sempre   GRANDIOSO ..... 

Ci  dovro'  studiare  sopra per mesi ... , non pensavo che  per dare l'istruzione filtra per ......  servissero tutte queste righe di codice ,  .....  SOB !!! ne  avro' ancora per anni prima di essere autosufficente con VB .

Grazie per l'aiuto e per il materiale su quale potro' ragionare e imparare

                                    Ciao Claudio P