ListBox1.RowSource Filter through ComboBox

[Kyletok]

I want to create a tool that controls the way invoices are handled on my Invoice recorder.
so right now I made a listbox and I have:

Private Sub UserForm_Initialize()


ListBox1.RowSource = "Invoices"


End Sub

which loads entries from the "Invoices" Table, from there I found I could do many useful things. There's one thing I can't find any good explanations online for.

I want to be able to filter these Invoices that are loaded into the listbox by the second column, which is "Accounts".

so if I entered "555" into ComboBox 1, (which loads unique account numbers found in the Invoices table) the Listbox would then refresh and only show Invoice rows where the second column equals "555".

Please help me figure out how I can accomplish this.

Thanks in advance to anyone willing to help!

Kyle,

 

[DanteAmor]

You can not load only the filtered data with rowsource, in these cases I filter the data on the sheet and copy them to a "temp" sheet, then I load the data from the "temp" sheet with the rowsource.

Try the following, create a sheet called "temp"

Private Sub ComboBox1_Change()
    Dim temp As Worksheet, wData As Range, wAd As String
    
    ListBox1.RowSource = "Invoices"
    If ComboBox1.Value <> "" Then
        Application.ScreenUpdating = False
        Set temp = Sheets("[COLOR=#0000ff]Temp[/COLOR]")
        Set wData = Range("Invoices")
        temp.Cells.Clear
        wData.AutoFilter Field:=2, Criteria1:=ComboBox1.Value
        wData.Copy
        temp.Range("A1").PasteSpecial xlPasteValues
        wAd = temp.Range("A1").CurrentRegion.Address
        ListBox1.RowSource = temp.Name & "!" & wAd
        If Sheets(wData.Parent.Name).AutoFilterMode Then Sheets(wData.Parent.Name).AutoFilterMode = False
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End If
End Sub

 

[Kyle123]

You probably don't want to use RowSource at all, have a look here: https://www.excelforum.com/tips-and-tutorials/1256583-filterable-listbox.html

 

[DanteAmor]

Another way to load without rowource, but with the disadvantage of not putting the column title

Private Sub ComboBox1_Change()
    Dim temp As Worksheet, wData As Range
    Set wData = Range("Invoices")
    ListBox1.RowSource = ""
    wData.AutoFilter Field:=2, Criteria1:=ComboBox1.Value
    ReDim rdata(wData.Rows.Count, wData.Columns.Count)
    For Each i In wData.SpecialCells(xlCellTypeVisible).Rows
        For j = 1 To wData.Columns.Count
            rdata(n, j - 1) = wData.Cells(i.Row, j)
        Next
        n = n + 1
    Next
    ListBox1.List = rdata
End Sub