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