如何在 Excel VBA 中使用 ComboBox 值作为条件自动筛选列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27896541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Auto Filter a column using ComboBox value as Criteria in Excel VBA
提问by Edーさん
I have a spreadsheet that contains a list of Phone brands and Phone models (Phone brands are in column A, and Phone models are in column B)
我有一个电子表格，其中包含电话品牌和电话型号的列表（电话品牌在 A 列中，电话型号在 B 列中）
I'm trying to do an ActiveX program that allows Users to select a Phone brand from ComboBox1 and Filter column A using the Value of the ComboBox1.
我正在尝试执行一个 ActiveX 程序，该程序允许用户使用 ComboBox1 的值从 ComboBox1 和筛选列 A 中选择电话品牌。
Here's what I have so far:
这是我到目前为止所拥有的：
Option Explicit
Sub AdvFilter()
Dim lw As Long
Dim lr As Long
lw = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & lw).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H1"), Unique:=True
End Sub
Private Sub ComboBox1_Change()
Dim lr As Long
Dim Sel As String
lr = Range("H" & Rows.Count).End(xlUp).Row
Sel = "H2:H" & lr
ComboBox1.ListFillRange = Sel
End Sub
I was able to populate comboBox1 with unique values from Column A, but I can't figure out how to filter it using the value of ComboBox1. Any help would be greatly appreciated.
我能够使用 A 列中的唯一值填充 comboBox1，但我无法弄清楚如何使用 ComboBox1 的值对其进行过滤。任何帮助将不胜感激。
回答by Sathish
you have only got the list of phone brands so far from column A and written them (unique brands) to column H and put it as the list of values in Combobox. Now based on the value in combobox, you need to display the list of the phone models belonging to that brand in a column (say column I)
到目前为止，您只从 A 列获得了电话品牌列表，并将它们（独特品牌）写入 H 列，并将其作为 Combobox 中的值列表。现在根据组合框中的值，您需要在一列中显示属于该品牌的手机型号列表（比如第一列）
Step 1: Get the phone brand selected from combobox. Refer this example VBA - Get Selected Value of Combobox
第 1 步：从组合框中选择手机品牌。请参阅此示例VBA - 获取组合框的选定值
Step 2: Get list of phone models under that phone brand. Let us say the phone brand is phone_brand
第 2 步：获取该手机品牌下的手机型号列表。让我们说手机品牌是phone_brand
Sub getPhoneModels()
Dim iCol As Integer
iCol = 1
Dim phone_brand As String
phone_brand=getPhoneBrandFromComboBox()
For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
If StrComp(.Cells(1, i), phone_brand) = 0 Then
.Cells(9, iCol) = .Cells(1, i)
iCol = iCol + 1
End If
Next i
End Sub
The above code will go through all the phone models in column B and write the ones with brand value as phone_brandin the column I (9)
上面的代码会遍历B列的所有手机型号，并写出phone_brand第一列（9）中具有品牌价值的型号
getPhoneBrandFromComboBox()function is the function written in first step using the help given in link. This function returns the phone brand selected using the comboBox
getPhoneBrandFromComboBox()function 是使用链接中给出的帮助在第一步中编写的函数。此函数返回使用组合框选择的手机品牌
