excelvbatextboxlistbox

Listbox dynamic search through textbox in Userform VBA

I have here a sample form below with dynamic search code from this reference.

img1

OR

img2

Code:

Private Sub txtSearch_Change()
Dim i As Integer
Dim j As Integer


With listData
    .MultiSelect = fmMultiSelectSingle
    .ListIndex = -1
    .MultiSelect = fmMultiSelectMulti


    For i = 0 To .ListCount - 1
        For j = 0 To .ColumnCount - 1
            If InStr(1, .Column(j, i), txtSearch.Text, vbTextCompare) Then
                .ListIndex = i
                .Selected(i) = True
            End If
        Next j
    Next i


End With
End Sub

What to update in the code above to only show this and without highlighting:

img3

OR

img4

Your help is greatly appreciated. Thank you.

Solution

  • Insert this snippet after the End With statement and adjust the object names to the actual

    Dim lb As MSForms.ListBox
Set lb = Me.ListBox1
ReDim keep(0 To lb.ColumnCount - 1, 0 To 0)
ptr = 0
For i = 0 To lb.ListCount - 1
    
        If lb.Selected(i) Then
            For j = 0 To lb.ColumnCount - 1
                keep(j, ptr) = lb.List(i, j)
            Next j
                ptr = ptr + 1
                ReDim Preserve keep(0 To lb.ColumnCount - 1, ptr)
        End If
    
Next i
If ptr <> 0 Then
ReDim Preserve keep(lb.ColumnCount - 1, ptr - 1)
End If
lb.List = WorksheetFunction.Transpose(keep)

    This will keep the rows which are selected in the listbox in the keep variable, and after creating it assign to the listbox to display.
    Me is referencing the form where the listbox is.
    Listbox1 is the name of the listbox.