How making a searchbar code in VBA for excel 2016?

Question

I have a code for Excel 2016 (that’s the version of the app my work is using) to filter results in a table accordingly to what I enter into the searchBar (ActiveX textbox).

I would love to have help please:

Private Sub TextBox1_Change()
    Dim dataSheet As Worksheet
    Dim dataRange As Range
    Dim searchText As String
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim rngCell As Range
    Dim rngRow As Range
    Dim foundMatch As Boolean

    On Error Resume Next
    Application.EnableEvents = False

    Set dataSheet = ThisWorkbook.Worksheets("Sheet1")

    ' Find the last row and last column of the table
    lastRow = dataSheet.Cells(dataSheet.Rows.Count, 1).End(xlUp).Row
    lastColumn = dataSheet.Cells(1, dataSheet.Columns.Count).End(xlToLeft).Column

    ' Set the data range based on the last row and last column
    Set dataRange = dataSheet.Range("A2", dataSheet.Cells(lastRow, lastColumn))

    ' Get the search text from the TextBox
    searchText = Me.TextBox1.Value ' Use the TextBox name from the Properties window

    ' Clear any previous filters
    dataRange.AutoFilter

    ' Apply the filter only if the search text is not empty
    If Len(searchText) > 0 Then
        ' Loop through each row in the data range
        For Each rngRow In dataRange.Rows
            foundMatch = False ' Reset the flag for each row
            For Each rngCell In rngRow.Cells
                ' Check if the cell value contains the search text
                If InStr(1, rngCell.Value, searchText, vbTextCompare) > 0 Then
                    ' If the cell contains the search text, set the flag and exit the inner loop
                    foundMatch = True
                    Exit For
                End If
            Next rngCell
            ' Hide or unhide the row based on whether a match was found
            rngRow.EntireRow.Hidden = Not foundMatch
        Next rngRow
    Else
        ' If the TextBox is empty, unhide all rows to show the original source data
        dataRange.Rows.Hidden = False
    End If

    Application.EnableEvents = True
End Sub

The code I’ve written above isn’t working and I don’t know why, please help me finding out what’s wrong. My table isn’t filtering itself.

Answer 1

I think I understood why it’s not working for me, it’s not appropriate for a table that is a ListObject. Instead I’ve coded another piece of code that is working that I am posting here

    Private Sub TextBox1_Change()
    
        Dim filterCriteria As String
        Dim ws As Worksheet
        Dim lo As ListObject
        Dim i As Integer
        Dim ConcatFormula As String
    
        Set ws = ThisWorkbook.Sheets("Sheet1")
    
        Set lo = ws.ListObjects("Table1")
    
        ' Prepare CONCATENATE formula
        ConcatFormula = ""
        For i = 1 To lo.ListColumns.Count - 1
            ConcatFormula = ConcatFormula & "RC" & i & ", "" """
            If i <> lo.ListColumns.Count - 1 Then
                ConcatFormula = ConcatFormula & ","
            End If
        Next i
    
        ' Check if helper column exists and create it if not
        On Error Resume Next
        Dim helperCol As ListColumn
        Set helperCol = lo.ListColumns("Helper")
        If helperCol Is Nothing Then
            Set helperCol = lo.ListColumns.Add
            helperCol.Name = "Helper"
            helperCol.DataBodyRange.FormulaR1C1 = "=CONCATENATE(" & ConcatFormula & ")"
        End If
        On Error GoTo 0
    
        ' Hide the helper column
        helperCol.Range.EntireColumn.Hidden = True
    
        If Me.TextBox1.Value = "" Then
            ' Show all data if TextBox is empty
            On Error Resume Next
            lo.AutoFilter.ShowAllData
            On Error GoTo 0
        Else
            ' Define the filter criteria
            filterCriteria = "*" & Me.TextBox1.Value & "*"
            ' Filter the helper column
            lo.Range.AutoFilter Field:=lo.ListColumns.Count, Criteria1:=filterCriteria, Operator:=xlFilterValues
        End If
    End Sub