filter a ListBox with a Combobox on VBA

Question

I'm working on a Userform and I have weeks trying to develop a code to filter a listbox depending of the value of a combobox.

The closest I have done is make a commandbutton to filter the table where the listbox feeds but it doesn't refresh the listbox.

I have seen on forums people doing things like I want but I have tried all of them with no results.

Private Sub CommandButton1_Click()
    If ComboBox1.Value <> "" Then ActiveSheet.ListObjects("Tabla2").Range.AutoFilter field:=2, Criteria1:=ComboBox1.Value
End Sub

Private Sub CommandButton2_Click()
    ActiveSheet.ListObjects("Tabla2").Range.AutoFilter field:=2
End Sub

Private Sub CommandButton3_Click()
    Unload UserForm2
    UserForm3.Show
End Sub

Private Sub UserForm_Initialize()
    For i = 2 To 30
        ComboBox1.AddItem Sheets("Proyectos - J.P.").Range("A" & i).Value
    Next i
End Sub

04-05-2017 Workbook Link https://drive.google.com/open?id=0B4B7v0UZxizCYnY2bVNTNURyLVU

In the WorkBook you will see 3 userforms, the Userform1 is Ok. The userform2 have the Combobox (Proyect Code) and the ListBox i want to filter. The userform3 is not ready yet, because i need to have on 100% the Userform2 to take a decision. Hope it helps. Regards

Answer 1

I have already faced a similar situation, but instead of a ComboBox, I needed to filter the ListBox based on the selection of other ListBox and the selection of an Option. The way I found to meet my need was to use a Pivot Table in a hidden sheet. It worked fine for me, bit not all data can be rearranged in a Pivot Table, so I will understand if my suggestion does not work for you.

1. First Step: set up a Pivot Table with your data source to be used in your ListBox. Pull the fields you want to filter in the Filters area. Create a dinamic named range with your data, like in the image:

=OFFSET('Certificates Pivot'!$A$5;0;0;COUNTA('Certificates Pivot'!$A$5:$A$50);2)

2. Second Step: create your UserForm. I set up 2 ComboBoxes as filters to the ListBox, but you can remove or add as many as you can, you'll just need to adjust your code. I also named the ranges that will be available in the list options of the ComboBoxes. So we'll have:

The UserForm's code will be something like this:

Private Sub UserForm_Initialize()

    ComboBox1.RowSource = "CustomerID"
    ComboBox2.RowSource = "SalesOrg"
    With ListBox1
        .RowSource = "Consult_List"
        .ColumnCount = 2
        .ColumnWidths = "60;90"
        End With

End Sub
Private Sub ComboBox1_Change()

    Dim SelectedCID As String
    Dim SelectedSO As String
        SelectedCID = ComboBox1.Text
        SelectedSO = ComboBox2.Text

    With Sheets("Certificates Pivot").PivotTables("Certificates_PivotTable")
        .ClearAllFilters
        If Not SelectedCID = "" Then .PivotFields("Customer ID").CurrentPage = SelectedCID
        If Not SelectedSO = "" Then .PivotFields("Sales Org.").CurrentPage = SelectedSO
        End With

    ListBox1.RowSource = "Consult_List"

End Sub
Private Sub ComboBox2_Change()

    Call ComboBox1_Change

End Sub

You can hide the sheet where your Pivot Table is, so when you filter it through your UserForm, it will update in the background. You should also set up your Pivot Table to update its cache to capture new inputs in your data source.

I hope it works for you! Let me know what were the results.