Filter UserForm Multicolumn ListBox with TextBox/ComboBox

[Alexandroid]

Hi Everyone,


I've been looking for an answer to this specific scenario and I haven't found it yet. If it exists, I'm sorry for wasting your time and would request the link to that answer please.


If not, here we go.


I'm creating a listbox in a userform that's using a rowsource from a sheet to list the multicolumn list.


This list is very big and I want to be able to filter through it using a combobox to specify which column to filter by and a textbox to specify the keywords to filter by.


I've found different iterations of these in pieces but not as a whole and I haven't been able to put it together.


Here is the code I have so far:

Private Sub UserForm_Initialize()


ListBoxCharges.RowSource = "SourceTable!A1:" & _ Sheets("SourceTable").Range("A1").SpecialCells(xlCellTypeLastCell).Address


With Me.ListBox1
    .MultiSelect = fmMultiSelectMulti
    .ColumnCount = Worksheets("SourceTable").UsedRange.Rows(1).Columns.Count
End With


With Me.comboboxFilter
    For i = 1 To Me.ListBox1.ColumnCount
        If Sheets("SourceTable").Cells(1, i).Value <> "" Then
            .AddItem Sheets("SourceTable").Cells(1, i).Value 'ComboBox is populating from a horizontal headers list.
        End If
    Next i
End With




Private Sub txtboxFilter_Change()


    Dim i As Long
    Dim sCrit As String
    
    'Add asterisks around text for all matches
   'UCase is used to make filter case-insensitive
   sCrit = "*" & Me.txtboxFilter.Text & "*"
    
    With Me.ListBox1
    
        .RowSource = "SourceTable!A1:" & Sheets("SourceTable").Range("A1").SpecialCells(xlCellTypeLastCell).Address
    
        For i = .ListCount - 1 To 0 Step -1
            If Not UCase(.List(i)) Like sCrit Then
                Debug.Print .Selected(i)
                .Selected(i) = False
                .RemoveItem i
            End If
        Next i
        
        .MultiSelect = fmMultiSelectMulti
        .ColumnCount = Worksheets("SourceTable").UsedRange.Rows(1).Columns.Count
    End With


End Sub

I get a "Unspecified Error" (80004005) When I get to the ".RemoveItem i" line which I've read is because I'm using RowSource to populate the list.


Is there an easier what to achieve all this?


Thank you for your help and let me know if you need more information.

 

[frabulator]

I personally find it a lot easier and faster to populate listboxes from a Collection rather than a RowSource.

Example:

Dim lst as Collection
lst = New Collection

With lst
   .Add ("BOB")
   .Add ("Marley")
End With

Dim Item as Variant
For Each Item In lst
   ListBox1.AddItem Item
Next Item

A good example of how to populate a listbox with a collection can be found here.

Hope that helps!
~Frab