Filter combox on userform during typing in it

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Filter combox on userform during typing in it

Post by YasserKhalil »

Hello everyone
I have filled a combobox on userform with the values from column 2, and I would like to able to type any letter or letters and filter the combox while typing.
This is my try but I couldn't adjust it

Code: Select all

Private arrData, arrFiltered

Private Sub UserForm_Activate()
    ComboBox1.Clear
    ComboBox1.List = Sheets(1).Range("B2:B" & Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row).Value
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    On Error Resume Next
    If arrFiltered = Empty Then Exit Sub
    If UBound(arrFiltered) = -1 Then Exit Sub
    On Error GoTo 0
    ComboBox1.Clear
    ComboBox1.List = arrFiltered
End Sub

Private Sub ComboBox1_Change()
    Dim strSearch As String, i As Long
    With Sheets(1)
        arrData = .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
        arrData = Application.Transpose(arrData)
    End With
    strSearch = ComboBox1.Text
    ComboBox1.AutoWordSelect = False
    arrFiltered = Filter(arrData, strSearch, True, vbTextCompare)
    ComboBox1.Clear
    For i = LBound(arrFiltered) To UBound(arrFiltered)
        ComboBox1.AddItem arrFiltered(i)
    Next i
    Me.ComboBox1.DropDown
End Sub

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter combox on userform during typing in it

Post by HansV »

Adapted from Filter combobox list as you type:

Code: Select all

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

Private Sub UserForm_Initialize()
    ComboBox1.MatchEntry = fmMatchEntryNone
    Call Combobox1_Populate
End Sub

Sub Combobox1_Populate()
    Dim arrIn As Variant, arrOut As Variant
    Dim i As Long, j As Long
    
    arrIn = Sheets(1).Range("B2:B" & Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row).Value
    ReDim arrOut(1 To UBound(arrIn), 1 To 1)

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

    ComboBox1.List = arrOut
End Sub
Best wishes,
Hans