通过在项目中的任何位置匹配字符串来过滤VBA组合框 [英] Filter a VBA combo box by matching string anywhere in an item

问题描述

在我的excel表中，我有一个组合框有很多值。目前，您可以通过键入字符串的前几个字母跳转到组合框中的项目。例如。

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.

• John Smith


• Ted James


• Phillip Price








• / li>
  

• John Smith
• Ted James
• Phillip Price
• Tom Hardy
• James Dean
• Chris Keaking

所以如果我开始键入 Te ，它会过滤到 Ted James 。

So if I started typing Te it would filter to Ted James.

但是我只知道姓氏，我输入 Dean ，没有什么会匹配，因为它只从字符串的开头而不是在其中搜索。同样，如果我键入 Jam ，它会过滤到 James Dean ，但不是 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 Jam it would filter to James Dean but not Ted James.

有一种方法来复制VBA的行为，比如 Select2插件，它会在字符串中的任何位置进行搜索并进行相应的过滤。

Is there a way to replicate the behaviour with VBA of something like the Select2 plugin which searches anywhere in the string and filters accordingly?

推荐答案

>尝试此解决方案 - 在您键入时，下拉显示和更新只显示部分匹配输入到组合框中的选项。它可能需要一些额外的工作，因为当你选择了一个有效的选择，列表仍然只过滤该项目，直到你清除组合框，但它可能会给你你需要什么。

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.

使用以下代码创建名为 mdlComboBox 的模块

Create a module called mdlComboBox with the following code

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

在 ThisWorkbook 模块中，添加以下内容以确保在工作簿打开时填充ComboBox：

In the ThisWorkbook module, add the following to ensure that the ComboBox is populated when the Workbook opens:

Public Sub Workbook_Open()
    InitCombobox1
End Sub

最后，向 Sheet1 添加一个ComboBox（名为 ComboBox1 以下代码到 Sheet1 模块：

Finally, add a ComboBox (named ComboBox1) to Sheet1 and add the following code to the Sheet1 module:

Private Sub ComboBox1_Change()
    FilterComboBox1 ComboBox1.Value
    ActiveSheet.Select
    ComboBox1.DropDown
End Sub

b $ b

Activesheet.Select 行强制组合框从头开始重新绘制下拉列表，仅显示由函数过滤的选项。在这个解决方案中，你必须跟踪总的选择集合，我在一个集合全局变量，但有一些情况下，它可以失去它的价值，所以它可能更好的硬代码或拉从一个表

The line Activesheet.Select forces 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.

这篇关于通过在项目中的任何位置匹配字符串来过滤VBA组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！