Excel Combobox filter for partial matches

Question

I have a spreadsheet to track some expenses. Since the number of rows can get fairly long, I decided to include a UserForm, in which the user can simply input the date, the amount and a category.

The categories can be chosen from a Combobox, which is populated as the UserForm is initialized. The problem I'm currently facing is the following:

The list of categories can get rather lengthy, and it is rather inconvenient for the user to search through the whole category list. So I would like the User to simply type in the category, and as he types, the Combobox-List would only show partial matches, from which he can then select the desired category.

Is this achievable?

I tried setting the .AutoWordSelect property to True, but that only shows the first partial match and as it turns out, is by default set to True.

UPDATE:

So thanks to @Doug Glancy, i was able to achieve what I intended with the following code:

Private Sub cboKategorie_Change()

Dim ws5 As Worksheet
Dim Rng As Range, r As Range
Dim search As String

search = "=" & cboKategorie.Value & "*"

Set ws5 = Worksheets(5)

With ws5

    .ListObjects("tblKategorien").Range.AutoFilter Field:=1, Criteria1:=search
    Set Rng = .Range("A2", .Range("A2").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)

End With

cboKategorie.Clear

For Each r In Rng

    cboKategorie.AddItem (r.Value)

Next r

ws5.ListObjects("tblKategorien").Range.AutoFilter Field:=1

End Sub

The Problem I have now is that the Change Event is also triggered, when I select something from the Combobox. This of course forces the macro to run again, and it filters again for the selection I made, but it never actually puts it in the text field. Is there a way to force the change event trigger to stop? Or put the selection into the text field?

Answer 1

I managed to solve this one, so if someone is facing a similar problem then here is the code, that worked for me.

It is not pretty and I'm not very happy with the workaround but it works.

Private Sub cboKategorie_Change()

Dim ws5 As Worksheet
Dim Rng As Range, r As Range
Dim search As String

search = "=" & cboKategorie.Value & "*"

Set ws5 = Worksheets(5)

With ws5

    Set Rng = .Range("A2", .Range("A2").End(xlDown))

    For Each r In Rng

        If r.Value = cboKategorie.Value Then Exit Sub

    Next r

    .ListObjects("tblKategorien").Range.AutoFilter Field:=1, Criteria1:=search
    Set Rng = .Range("A2", .Range("A2").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)

End With

cboKategorie.Clear

For Each r In Rng

    cboKategorie.AddItem (r.Value)

Next r

ws5.ListObjects("tblKategorien").Range.AutoFilter Field:=1

End Sub

I ended up just checking if the value in the Combobox is equal to one of the values in the Table where I keep the categories stored. If the values are the same then the sub is left before anything happens.

Note, that in order for this to work, one has to set the MatchEntry-Property to fmMatchEntryNone.

I hope that this will help someone in the future facing the same issues.