Filter a VBA combo box by matching string anywhere in an item

Question

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
• Tom Hardy
• James Dean
• Chris Keaking

So if I started typing Te it would filter to 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.

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?

Answer 1

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 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

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

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

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.

Answer 2

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.

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.

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.