Filter Combo Box list items based on partially entered text

[Balsams]

I had the answer to this one but my sleep deprived brain can't seem to recreate the search string I used to find it...

I have a combo box that is populated by a range on another sheet I have defined. What I would like is:

-to have the drop down list automatically drop down once the user begins entering text
-to have the list of items shown reflect what the user is typing in (so if "G" is entered then only items starting with "G" are shown...which is further reduced once "Gr" is entered and so on).

Is this possible?

Thanks in advance. Virtual high five for whoever can help.

[mikerickson]

What kind of combo box is it? Userform, ActiveX or Forms menu?

[Balsams]

It's from the Control Toolbox.

[Balsams]

I think I am on my way to solving this using a combination of formulas and VBA macros...but I get the feeling that there is a simple way to do this I am missing.

Once I get things working properly I will post my results. Hopefully a smart person will come up with the easy way and save me some grief.

[mikerickson]

This code for a Userform might be adapted for an ActiveX ComboBox. My version does not support ActiveX, but I understand that UF code transfers over almost unchanged.

Dim singleFlag as Boolean
Dim ufEventsDisabled as Boolean

Private Sub ComboBox1_Change()
    Dim topVal As Variant
    Dim mySStart As Long
    
    If ufEventsDisabled Then Exit Sub
    If singleFlag Then singleFlag = False: Exit Sub
    
    With Me.ComboBox1
        mySStart = .SelStart
        .DropDown
        topVal = vbNullString
        topVal = Application.Match(.Text, DataList, 1)
        If IsNumeric(topVal) Then
            .TopIndex = topVal
        End If
        topVal = Application.Match(.Text & "*", DataList, 0)
        If IsNumeric(topVal) Then
            .TopIndex = topVal - 1
            .ListIndex = topVal - 1
            .SelStart = mySStart
            .SelLength = Len(.Text) - mySStart
        End If
    End With
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    singleFlag = (KeyCode = 8)
End Sub

Function DataList() As Variant
    Dim xVal As Variant
    xVal = Me.ComboBox1.List
    DataList = Application.Transpose((Application.Index(xVal, 0, 1)))
End Function

[Balsams]

I understand the basics of the code but not how to get it to work with my lists. What are the changeable variables?

The file I'm working on is sensitive info but I'll try to put together a sample version so you can see what I'm trying to do.

Thanks for your help btw.

[mikerickson]

My Mac doesn't support ActiveX, so someone else will have to look at your file.
The idea of my code is to 1) have the List sorted ascending. 2) Use MATCH(ComboBox.Value, ComboBox.List) to find the closest match to the typed value and 3) use that MATCH value to set the .TopIndex property of the combobox's list.

[Balsams]

Here is the sample. The original has price lists and customer lists with over 5000 entries each.

As you can see I can get the combo box to work...sort of. Because it only updates once something is entered it is always showing results one step behind.

Ie. If you enter "D" it will show all matches for "blank". If you enter "e" (so now you have "De") it will show all matches for "D".

Plus once you have a few thousand entries it really slows down.

There are other issues I have but I will be starting some new threads for them.

Thanks!