Listbox filtering using textbox

[RAires]

Hello everyone,

I need help with my code. I have a listbox in my userform that displays all the data in Sheet1. From the userform the user can select a row and then Delete/Add new rows and edit existing rows. Given this i want to make a filter where the user can search for a word and it will show only the rows that have that word. For example if you search for a serial number of a item it will show you only the row where that or those items are so the user can then delete or edit that row.

I managed to make the code highlight when you search but i only managed to do it if you search words that are present in the first column and i've been stuck ever since.

Any help would be appreciated.

Code:

Private Sub txtsearch_Change()
        'searches lstdisplay for match and hightlights result. Need to filter results.
    Dim i As Long
    Dim sFind As String

    sFind = Me.txtsearch.Text

    If Len(sFind) = 0 Then
        Me.lstDisplay.ListIndex = -1
        Me.lstDisplay.TopIndex = 0
    Else
        For i = 0 To Me.lstDisplay.ListCount - 1
            If UCase(Left(Me.lstDisplay.List(i), Len(sFind))) = UCase(sFind) Then
                Me.lstDisplay.TopIndex = i
                Me.lstDisplay.ListIndex = i
                Exit For
            End If
        Next i
    End If
End Sub

 

[Anthony47]

Please see this discussion: Filter a listbox containing multiple fields/columns using a single searchbox
The file that I developed for user jayfromthebay can still be downloaded from the dropbox link

In my file the textbox filters the content of the listbox using its "Change" event

Bye

 

[RAires]

Hi Anthony, thanks for the reply.

I tried your code but i cant test it, i debug it and dont get anything, but when i try to run the form it gives me the "Run time error '70: - permission denied"

 

[Anthony47]

Press the ShowForm button and a Form with a ListBox and a Textbox will appear.
Columns B:F of the first sheet are used to populate the listbox; when you enter text in the textbox only the rows that contain that string in one of the Cells (any of the 5 columns) will populate the ListBox.

You should start from this instructions, working on the published workbook. After you see how it works you'll customize the code to your situation.

For those that prefer the code shared in the forum, the workbook has a userform with one "5 columns listbox" (because the source table is 5 columns wide) and one textbox used for filtering the listbox content. The
The Textbox Change event triggers the following code:

Private sArr(), iSort As Long

Private Sub TextBox1_Change()
'ListBox content is updated whenever the (filter) textbox is modified
Dim SRan As Range, ohYes As Boolean, rCount As Long
'
Set SRan = Range(Range("B2"), Range("B2").End(xlDown).End(xlToRight))  'This is the Row Source
ReDim sArr(1 To SRan.Columns.Count, 1 To SRan.Rows.Count)
For i = 1 To SRan.Rows.Count
    ohYes = False
    For j = 1 To SRan.Columns.Count
        If InStr(1, SRan.Cells(i, j).Value, TextBox1.Value, vbTextCompare) > 0 Then
            ohYes = True
            Exit For
        End If
    Next j
    If ohYes Then
        rCount = rCount + 1
        For j = 1 To SRan.Columns.Count
            sArr(j, rCount) = SRan.Cells(i, j).Value
        Next j
    End If
Next i
'Resize sArr:
If rCount > 0 Then
    ReDim Preserve sArr(1 To j - 1, 1 To rCount)
Else
    ReDim Preserve sArr(1 To j - 1, 1 To 1)
End If
'Sort the array:
If UBound(sArr, 2) > 1 Then
'sArr is a true array:
    sArr = bbSort(Application.WorksheetFunction.Transpose(sArr))     'REMOVE this line if you don't need Sort
    ListBox1.List = sArr
Else
'If one line only it is a bit more complex:
    Me.ListBox1.Clear
    Me.ListBox1.AddItem
    For i = 1 To UBound(sArr)
        Me.ListBox1.Column(i - 1, 0) = sArr(i, 1)
    Next i
End If
End Sub

If you do not succede in customizing it for your data then share a sample workbook and we shall see

Bye