在 VBA 上使用组合框过滤列表框

filter a ListBox with a Combobox on VBA

提问by Franco.Pumarino

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 Linkhttps://drive.google.com/open?id=0B4B7v0UZxizCYnY2bVNTNURyLVU

04-05-2017 工作簿链接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

在工作簿中，您将看到 3 个用户表单，用户表单 1 是好的。userform2 有组合框（项目代码）和我想要过滤的列表框。userform3 还没有准备好，因为我需要 100% 的 Userform2 才能做出决定。希望能帮助到你。问候

采纳答案by J. L. Muller

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.

我已经遇到过类似的情况，但是我需要根据其他ListBox的选择和Option的选择来过滤ListBox，而不是ComboBox。我发现满足需求的方法是在隐藏工作表中使用数据透视表。它对我来说很好用，并不是所有的数据都可以在数据透视表中重新排列，所以如果我的建议对你不起作用，我会理解。

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:

1. 第一步：使用要在 ListBox 中使用的数据源设置数据透视表。在过滤器区域中拉出要过滤的字段。使用您的数据创建一个动态命名范围，如图所示：

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

1. 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:

2. 第二步：创建您的用户表单。我设置了 2 个组合框作为列表框的过滤器，但您可以删除或添加尽可能多的，您只需要调整您的代码。我还命名了将在 ComboBox 的列表选项中可用的范围。所以我们将有：

The UserForm's code will be something like this:

UserForm 的代码将是这样的：

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.

我希望这个对你有用！让我知道结果如何。