excel Vba dynamic filter help

[cizzett]

OK Excel gurus,
I have a spreadsheet I want to use a text box change event to dynamically filter the table by the data in the text box as it is entered.

Also I would prefer if it searched through all the table columns so I can search using different data points and still find it hopefully without having to make a search box for each column.

Here is what I am trying now but keeps erroring out on me.

Advice greatly appreciated.

Private Sub SearchBox_Change()Dim BCST As ListObject
Dim BS As Worksheet
Set BS = ThisWorkbook.Sheets("Blind Count")
Set BCST = BS.ListObjects("BCST")




If Len(SearchBox.Value) = 0 Then
    BCST.AutoFilterMode = False
    Else
    If BCST.AutoFilterMode = True Then
    BCST.AutoFilterMode = False
    End If
    BCST.DataBodyRange.AutoFilter , "&textbox1.Value&"
End If


End Sub

 

[jasonb75]

You would need to loop through the columns for this to even have a chance of working.

You will most likely encounter problems with what you're attempting. The most obvious being the wrong column(s) being filtered when common values are found.

The red bits need deleting "&textbox1.Value&"
As you have it, you're looking for what is between the double quotes, not what is typed into the box.