How to filter listbox values based on a Textbox value

Question

I have a textbox and a listbox on userform. I want to filter the values in listbox based on the value I enter in Textbox. Sheet named TMP has the values and I filter it based on textbox change event but it quits automatically when adding that values to listbox.

Private Sub Textbox1_Change()
'On Error Resume Next
Dim fCell As Range, MyArr As Variant, i As Long

With TMP
    .AutoFilterMode = False
    .Range("A1").AutoFilter
    .Range("A1").AutoFilter Field:=1, Criteria1:=Me.TextBox1.Value
End With

ListBox1.RowSource = ""
i = 0

For Each fCell In TMP.Range("A1:A" & TMP.Range("A" & TMP.Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    Me.ListBox1.AddItem fCell.Value, i
     i = i + 1
Next fCell


End Sub

Ralph · Accepted Answer · 2017-03-18 21:25:23Z

19

I sure hope the following piece of code is what you are looking for.

Private Sub Textbox1_Change()

Dim i As Long
Dim arrList As Variant

Me.ListBox1.Clear
If TMP.Range("A" & TMP.Rows.Count).End(xlUp).Row > 1 And Trim(Me.TextBox1.Value) <> vbNullString Then
    arrList = TMP.Range("A1:A" & TMP.Range("A" & TMP.Rows.Count).End(xlUp).Row).Value2
    For i = LBound(arrList) To UBound(arrList)
        If InStr(1, arrList(i, 1), Trim(Me.TextBox1.Value), vbTextCompare) Then
            Me.ListBox1.AddItem arrList(i, 1)
        End If
    Next i
End If
If Me.ListBox1.ListCount = 1 Then Me.ListBox1.Selected(0) = True

End Sub

Note that this sub does not make use of the AutoFilter on the sheet TMP. Therefore, the sub is a bit faster. Also, if you wish to filter your data on the sheet, this sub won't delete / change your current filter settings.

The line at the end If Me.ListBox1.ListCount = 1 Then Me.ListBox1.Selected(0) = True is not really necessary but rather for your convenience. It ensures that the item is automatically selected in the ListBox if there is only 1 item in the list.

answered Mar 18, 2017 at 21:25

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rohan Over a year ago

Wow you are awesome. Thanks a lot!

Ammadeux Over a year ago

How to do it with a multi column Listbox?

xris23 Over a year ago

For multicolumn listbox, you can replace Me.ListBox1.AddItem arrList(i, 1) by Me.Listbox.AddItem Listbox.List(0, 0) = arrList(i, 1) Listbox.List(0, 1) = arrList(i, 2)

kadrleyn · Accepted Answer · 2021-05-02 18:48:26Z

According to the value in the textbox, data can be filtered in a multi-column listbox. Also, the listbox column to be filtered can be selected from a combobox.

For example, VBA codes that are triggered by clicking the "Search" button to search in the first column of the listbox (column with names):

deg2 = TextBox13.Value
Select Case ComboBox1.Value
Case "Name"
For sat = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Set deg1 = Cells(sat, "A")
If UCase(deg1) Like UCase(deg2) & "*" Then
ListBox1.AddItem
ListBox1.List(s, 0) = Cells(sat, "A")
ListBox1.List(s, 1) = Cells(sat, "B")
ListBox1.List(s, 2) = Cells(sat, "C")
ListBox1.List(s, 3) = Cells(sat, "D")
ListBox1.List(s, 4) = Cells(sat, "E")
ListBox1.List(s, 5) = Cells(sat, "F")
ListBox1.List(s, 6) = Cells(sat, "G")
ListBox1.List(s, 7) = Cells(sat, "H")
ListBox1.List(s, 8) = Cells(sat, "I")
ListBox1.List(s, 9) = Cells(sat, "J")
ListBox1.List(s, 10) = Cells(sat, "K")
ListBox1.List(s, 11) = Cells(sat, "L")
s = s + 1
End If: Next

Source,sample file link

Collectives™ on Stack Overflow

How to filter listbox values based on a Textbox value

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related