Filter combobox list as you type

[eduzs]

Does anyone know how to implement a "filter as you type" in a combobox list in a userform vba?

Thanks

 

[RickXL]

Hi,

This should get you started:

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Call Combobox1_Populate
End Sub

Private Sub UserForm_Initialize()
    Call Combobox1_Populate
End Sub

Sub Combobox1_Populate()

    Dim arrIn As Variant, arrOut As Variant
    Dim i As Long, j As Long
    
    arrIn = Sheet1.Range("A2:G7")
    ReDim arrOut(1 To UBound(arrIn), 1 To 2)

    For i = 1 To UBound(arrIn)
        If arrIn(i, 1) Like ComboBox1.Text & "*" Then
            j = j + 1
            arrOut(j, 1) = arrIn(i, 1)
            arrOut(j, 2) = arrIn(i, 2)
        End If
    Next

    ComboBox1.List = arrOut

End Sub

The Combobox1_Populate code starts from a range on Sheet1 and puts that information into an array.
That data is then read row by row and if it matches the text entered into the ComboBox then it is copied to the output array.
That array is used to populate the ComboBox.

The Combobox1_Populate is called when the UserForm is initialized and everytime a key is released in the ComboBox.

I used a ComboBox with two columns and populate it from the worksheet. You can change both those to suit.
It works best if the ComboBox MatchEntry property is set to "2-fmMatchEntryNone" otherwise it guesses the word and the filter only finds one entry.

 

[RickXL]

If you need only a single column in the ComboBox then it works out more easily:

Sub Combobox1_Populate(Optional fltr As String)
    ComboBox1.List = Filter(Array("qqq", "qqwww", "qqttt", "qwer"), fltr)
End Sub

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Call Combobox1_Populate(ComboBox1.Text)
End Sub

Private Sub UserForm_Initialize()
    Call Combobox1_Populate
End Sub

This time I assumed that the ComboBox list would be supplied in the code ao that a one dimensional array could be used permitting the Filter method to filter the list.

As you can see, the answer optimum will depend on your exact requirements.

 

[eduzs]

Thank you!
I'll try these solutions and update the post later.

 

[eduzs]

Thanks,

I made this code that works great, it's a workaround. Not perfect and beautiful, but seems to work.

ListaAT is a array populated before show the userform.

Private Sub CB1_Change()

Dim x As Integer

With CB1

    For x = 0 To UBound(ListaAT)

        If UCase(.Value) = UCase(ListaAT(x)) And Len(.Value) >= 1 Then

            Call AutoTexto.Roda_AutoTexto
            Exit Sub

        End If

    Next x

    .Visible = False

    Do While .ListCount > 0

        .RemoveItem (0)

    Loop

    For x = 0 To UBound(ListaAT)

        If .Value = "" Then

            .AddItem ListaAT(x)

        Else

            If UCase(Left(ListaAT(x), Len(.Value))) = UCase(.Value) Then .AddItem ListaAT(x)

        End If

    Next x

    .Visible = True
    .SetFocus
    .DropDown

End With

End Sub

 

[RickXL]

I am glad you got it working and thanks for the update.

Regards,