How to search ListBox as the user types in TextBox
Category: [Excel] Demo Available
How to search ListBox as the user types in TextBox
Requirement:
The user needs some help with the code below, it searches the contents of the listbox as the user types in the textbox and work fine. However if the user miss types and use backspace nothing happens.
Code:
private Sub TextBox10_Change()
Dim j As Long
Dim testString As String
testString = LCase("*" & TextBox10.text & "*")
With ListBox1
For j = .ListCount - 1 To 0 Step -1
If (Not (LCase(.List(j, 0)) Like testString) And (Not (LCase(.List(j, 1)) Like testString))) _
And (Not (LCase(.List(j, 2)) Like testString) And (Not (LCase(.List(j, 3)) Like testString))) Then
.RemoveItem j
End If
Next j
End With
End Sub
Solution:
Code:
Option Explicit
Private Sub TextBox1_Change()
Dim j As Long, testString As String
testString = "*" & TextBox1.Text & "*"
With Me.ListBox1
.Clear
.List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
If .ListIndex = -1 And Len(TextBox1.Text) Then
For j = .ListCount - 1 To 0 Step -1
If (Not (LCase(.List(j, 0)) Like testString) And (Not (LCase(.List(j, 1)) Like testString))) _
And (Not (LCase(.List(j, 2)) Like testString) And (Not (LCase(.List(j, 3)) Like testString))) Then .RemoveItem j
Next j
End If
End With
End Sub
Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 10
ListBox1.List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by pike.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to auto-generate unique ID in Excel VBA UserForm |
| How to bring an excel userform message box above all other windows |
| How to calculate userform textbox and cell value for label caption |
| How to pause a macro to allow user to select a cell |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.