search bar for a listbox EXCEL

Question

I have a user form with a listbox that is autopopulated with the excel table rows as the form is opened,

lstDisplay.ColumnCount = 11
lstDisplay.RowSource = "A1:L65356"

i need to add a search bar that updates the list box and show only the row/rows that contains the value that i searched. now i added a text box Textfind and a button find. and to be honest i have no idea how to do that.

i tried something like :

Private Sub find_Click()
Dim ws As Worksheet
Dim numRow As Integer
Dim found As Boolean
Set ws = ThisWorkbook.Worksheets("resdata")
For numRow = (ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
        If nameTxtBox.Value = 

    (NO IDEA WHAT I CAN USE HERE )

            found = True
            Exit For
        End If
Next numRow
If found = False Then
    MsgBox "No Match Found!", vbCritical
    lstDisplay.Clear

End If

End Sub

Thanks everyone.

Answer 1

There is no button, this will filter as you type and add back with backspace.

This sub will remove rows based off of item (row,col) matchability. I.e. -"al" will remove any row that doesn't have an item starting with al in any of its columns.

Public Sub RemoveLB(Nme As String, LB As MSForms.ListBox)
Dim i As Integer
Dim lbv As String
Dim temp As String
Dim y As Long
Dim x As Long
Dim z As Long
Dim slctd As String
  
For i = LB.ListCount - 1 To 0 Step -1
    j = 0
    For x = LB.ColumnCount To 1 Step -1
        temp = ""
        If LB.List(i, x - 1) <> vbNullString Then
            lbv = LB.List(i, x - 1)
            temp = Mid(lbv, 1, Len(Nme))
            If temp <> Nme Then
                j = j + 1
            End If
        Else
            j = j + 1
        End If
    Next
    If j = LB.ColumnCount Then
        If LB.selected(i) = False Then
            If LB.List(i) <> "" Or LB.List(i) <> vbNullString Then
                LB.RemoveItem (i)
            End If
        End If
    End If
Next

End Sub

This sub will fill the listbox with a data sheet matching multipage tab name

Public Sub FillListBox(UF As UserForm, TBV As String, LB As MSForms.ListBox, DataSheet As String)
Dim LastCol As Long
Dim DCol As Long
Dim DLR As Long
Dim temp As String
Dim y As Long
Dim j As Long
Dim i As Long
Dim slct() As Long
Dim z As Long
DLR = 0
LastCol = ThisWorkbook.Worksheets(DataSheet).Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To LastCol
    If ThisWorkbook.Worksheets(DataSheet).Cells(Rows.Count, i).End(xlUp).Row >= DLR Then
        DLR = ThisWorkbook.Worksheets(DataSheet).Cells(Rows.Count, i).End(xlUp).Row
    End If
Next
LB.ColumnCount = LastCol
z = 0
For i = LB.ListCount - 1 To 0 Step -1
    If LB.selected(i) = False Then
        If LB.List(i) <> "" Or LB.List(i) <> vbNullString Then
            LB.RemoveItem (i)
        End If
    Else
        ReDim slct(z)
        slct(z) = LB.List(i)
        z = z + 1
    End If
Next
For i = 1 To DLR
        LB.AddItem
    For j = 1 To LastCol
        LB.List(i - 1, j - 1) = ThisWorkbook.Worksheets(DataSheet).Cells(i, j).Value
    Next
Next
For i = LB.ListCount - 1 To 0 Step -1
    For j = z - 1 To 0 Step -1
        If LB.List(i) = slct(j) Then
            LB.selected(i) = True
        End If
    Next
Next
End Sub

This will generate appropriate variables needed to fill the list box -FillListBox(Userform name, textbox value, listbox object, data sheet name). My Control names are..ListBoxPage1...TextBoxPage1..Page1 etc.

Public Sub FillVar()
'in Userform Code
Dim TBName As String
Dim LBName As MSForms.ListBox
Dim DS As String
Dim TBV As String
Dim i As Long
For i = 1 To Sheets.Count
    If Me.MultiPage1.SelectedItem.Caption = Sheets(i).Name Then
            Set LBName = Me.Controls("ListBox" + Me.MultiPage1.SelectedItem.Name)
            TBName = "TextBox" + Me.MultiPage1.SelectedItem.Name
            TBV = Me.MultiPage1(Me.MultiPage1.SelectedItem.Name).Controls(TBName).Value
            DS = Me.MultiPage1.SelectedItem.Caption
            Call FillListBox(DBF, TBV, LBName, DS)
    End If
Next
End Sub

This sub compares keypress values and calls fill and/or remove

Public Sub TextBoxCalls(TB As MSForms.TextBox, LB As MSForms.ListBox, keycode As MSForms.ReturnInteger)
'in Userform Code
If keycode.Value = 8 Then 'backspace
    Call FillVar
    Call RemoveLB(TB.Value, LB)
Else
If TB.Value = "" Or TB.Value = vbNullString Then 'empty
    Call FillVar
Else
'alphabet
If (keycode.Value < 91 & keycode.Value > 64) Or (keycode.Value > 96 & keycode.Value < 123) Or (keycode.Value < 58 & keycode.Value > 47) Or keycode.Value = 20 Then
    Call RemoveLB(TB.Value, LB)
End If
End If
End If
End Sub

Fully fills first open Pages Listbox

Public Sub UserForm_Initialize()
'in Userform Code
Application.Visible = True
Call FillVar
End Sub

Fully Fills ListBox based off changing pages in mulitpage

Private Sub MultiPage1_Change()
'in Userform Code
Call FillVar
End Sub

Calls sub that evaluates keypress (shown above)

Private Sub TextBoxPage5_KeyUp(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
'in Userform Code
Call TextBoxCalls(TextBoxPage5, ListBoxPage5, keycode)

End Sub

Answer 2

You might try something like this :

   Private Sub find_Click()
Dim ws As Worksheet
Dim numRow As Integer
Dim found As Boolean

Dim MyRng As Range

Set MyRng = Range("A1:A" & Sheets("resdata").Cells(Sheets("resdata").Rows.Count, "A").End(xlUp).Row)


Set ws = ThisWorkbook.Worksheets("resdata")

        For Each C In MyRng
          If C.Value Like nameTxtBox.Value Then

            found = True
            Exit For
          End If
        Next

If found = False Then
    MsgBox "No Match Found!", vbCritical
    lstDisplay.Clear

End If

End Sub