How to filter listbox values based on a Textbox value

by

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.

enter image description here

More Related Contents:

  1. VBA Excel Populate ListBox with multiple columns
  2. How to get selected value in multicolumn listbox
  3. VBA – destroy a modeless UserForm instance properly
  4. Excel VBA Userform – Execute Sub when something changes
  5. Copy filtered data to another sheet using VBA
  6. Excel Useform: How to hide application but have icon in the taskbar
  7. Somehow it’s not copying [closed]
  8. VBA macro that search for file in multiple subfolders
  9. So, I have 6 “master” files to then divide into 40 separate files
  10. Getting ScriptControl to work with Excel 2010 x64
  11. Count and Highlight keywords within phrases
  12. Transpose a range in VBA
  13. Protecting cells in Excel but allow these to be modified by VBA script
  14. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  15. Detect merged cells in VBA Excel with MergeArea
  16. Parse HTML content in VBA
  17. How do I name a spreadsheet automatically, by referencing two cells?
  18. Excel VBA Delete Rows
  19. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  20. Should I turn .CutCopyMode back on before exiting my sub procedure?
  21. VBA: How to delete filtered rows in Excel?
  22. Determine whether user is adding or deleting rows
  23. Removing the password from a VBA project
  24. Iterate over slicer via VBA and select a single item each time
  25. Loop through Excel Sheets
  26. How to add items to a combobox in a form in excel VBA?
  27. How can I open this VBA workbook faster?
  28. How can I automate Save as dialog box in IE11 using VBA?
  29. How do I get a list of unique values from a range in Excel VBA?
  30. Query my excel worksheet with VBA

Leave a Comment