通过匹配项目中任意位置的字符串来过滤 VBA 组合框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26281542/
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
Filter a VBA combo box by matching string anywhere in an item
提问by harryg
In my excel sheet I have a combo box with many values. Currently you can jump to an item in the combo box simply by typing the first few letters of the string. E.g. let's say I have these items in my combobox.
在我的 excel 表中，我有一个包含许多值的组合框。目前，您只需键入字符串的前几个字母即可跳转到组合框中的项目。例如，假设我的组合框中有这些项目。
- John Smith
- Ted James
- Phillip Price
- Tom Hardy
- James Dean
- Chris Keaking
- 约翰·史密斯
- 泰德詹姆斯
- 菲利普·普莱斯
- 汤姆哈迪
- 詹姆斯·迪恩
- 克里斯·基金
So if I started typing Teit would filter to Ted James.
所以如果我开始打字，Te它会过滤到Ted James.
But say I only know the surname and I type in Dean, nothing would match as it only searches from the start of the string, not within it. Likewise if I typed Jamit would filter to James Deanbut not Ted James.
但是假设我只知道姓氏并输入Dean，则不会匹配任何内容，因为它仅从字符串的开头搜索，而不是在其中搜索。同样，如果我输入Jam它会过滤到James Dean但不是Ted James.
Is there a way to replicate the behaviour with VBA of something like the Select2 pluginwhich searches anywhere in the string and filters accordingly?
有没有办法用 VBA 复制类似Select2 插件的行为，它会搜索字符串中的任何位置并相应地过滤？
回答by Blackhawk
Try this solution - as you type, the dropdown displays and updates to show only the choices which partially match what is typed into the combobox. It may require some additional work, since when you have selected a valid choice, the list is still filtered for only that item until you clear the combobox, but it might give you what you need.
尝试此解决方案 - 在您键入时，下拉列表会显示并更新以仅显示与组合框中键入的内容部分匹配的选项。这可能需要一些额外的工作，因为当您选择了一个有效的选项时，在您清除组合框之前，列表仍仅针对该项目进行过滤，但它可能会为您提供所需的内容。
Create a module called mdlComboBoxwith the following code
创建一个mdlComboBox使用以下代码调用的模块
Public colChoices As Collection
Public Sub InitCombobox1()
Set colChoices = New Collection
With colChoices
.Add "John Smith"
.Add "Ted James"
.Add "Phillip Price"
.Add "Tom Hardy"
.Add "James Dean"
.Add "Chris Keaking"
End With
FilterComboBox1 ""
End Sub
Public Sub FilterComboBox1(strFilter As String)
Sheet1.ComboBox1.Clear
For Each strChoice In colChoices
If InStr(1, strChoice, strFilter) <> 0 Then
Sheet1.ComboBox1.AddItem strChoice
End If
Next
End Sub
In the ThisWorkbookmodule, add the following to ensure that the ComboBox is populated when the Workbook opens:
在ThisWorkbook模块中，添加以下内容以确保在工作簿打开时填充 ComboBox：
Public Sub Workbook_Open()
InitCombobox1
End Sub
Finally, add a ComboBox (named ComboBox1) to Sheet1and add the following code to the Sheet1module:
最后，添加一个 ComboBox（名为ComboBox1）Sheet1并将以下代码添加到Sheet1模块中：
Private Sub ComboBox1_Change()
FilterComboBox1 ComboBox1.Value
ActiveSheet.Select
ComboBox1.DropDown
End Sub
The line Activesheet.Selectforces the combobox to redraw the dropdown from scratch, showing only the choices filtered by the function. In this solution, you have to keep track of the total set of choices, which I did in a Collection global variable, but there are situations in which it can lose its value, so it might be better to hard code or pull from a sheet instead.
该行Activesheet.Select强制组合框从头开始重新绘制下拉列表，仅显示由函数过滤的选项。在此解决方案中，您必须跟踪我在 Collection 全局变量中所做的全部选择集，但在某些情况下它可能会失去其价值，因此最好进行硬编码或从工作表中提取反而。
回答by Ronaldo
I tried the exact same code and got many crashes.
I made some minor changes and it works fine for me.
My first problem was to put something on the combobox right from the start.
I do prefer to initialize the ComboBox and populate it with the worksheet_activate() applied in the Sheet1 code.
我尝试了完全相同的代码，但发生了很多崩溃。
我做了一些小改动，对我来说效果很好。
我的第一个问题是从一开始就在组合框上放一些东西。
我更喜欢初始化 ComboBox 并使用在 Sheet1 代码中应用的 worksheet_activate() 填充它。
Private Sub worksheet_activate()
InitCombobox1
With ComboBox1
.AddItem "John Smith"
.AddItem "Ted James"
.AddItem "Phillip Price"
.AddItem "Tom Hardy"
.AddItem "James Dean"
.AddItem "Chris Keaking"
End With
End Sub
I don't know for you guys, but the previous answer code did not accept when I choose (or type) anything that comply with the list.
我不知道你们，但是当我选择（或键入）符合列表的任何内容时，以前的答案代码不接受。
To solve it, I did a IF checking the INDEX of the my entrance. If the selected item was in the list already, don't clear the dropdownlist.
为了解决这个问题，我做了一个 IF 检查我入口的 INDEX。如果所选项目已在列表中，请不要清除下拉列表。
Public Sub FilterComboBox1(strFilter As String)
If Sheet1.ComboBox1.ListIndex > -1 Then
Else
Sheet1.ComboBox1.Clear
For Each strchoice In colChoices
If InStr(1, strchoice, strFilter) <> 0 Then
Sheet1.ComboBox1.AddItem strchoice
End If
Next
End If
End Sub
But there's yet someting to solve. I'm using autocomplete, and this avoid the search when Excel try to guess the item i'm look for. For now, I solved it by pressing DEL. This makes it turn back to the search.
但还有一些问题需要解决。我正在使用自动完成功能，这样可以避免在 Excel 尝试猜测我要查找的项目时进行搜索。现在，我通过按 DEL 解决了它。这使它返回到搜索。
