How can I implement search within more than one field in text box?

Question

I have been trying to make/create a search box.

My dream scenario would be a dynamic search box that could actually look for what is inputted with in the given table and then filter on all columns. And when the search box is empty all and any filters needs to be removed or cleaned up and that is why I have written my code within a if function.

I have been searching and watching videos for 8 days (well at night time after work) googling and searching even on different questions (with or without answers) here, it doesn't seem possible to search for more then one Field at the time? (Or is it, if so how do I archive this?)

My first field with in my table (the entire column and all the cells) will always contain numbers and this will never change, the second column will not contain anything, but when the cells have any value (usually text) I need to search for even these values. Me changing the code and liking to field 1 doesn't work at all and gives error and also since field one is just numbers the search box seem to be wanting searching on exact numbers even though I have tried to code this with wildcards.

Also what I have written so far give me errors I cant get my head around, I get 'Object is needed' on a popup window and then under the local window, 'Me' and 'WS' without actual value in under value filed/column.

The TextBox1 is of curse set on 'Change' and cell is linked to A9.

another update to the question and problem, when the Else function gets activated (this is when I remove input from box) the the table heads looses those click buttons (those that can used to manually filter/sort with), this is a massive issue and I cant understand what I'm doing to provoke this unwanted outcome.

Would apricate some help on this subject.

Private Sub TextBox1_Change()
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    
    Application.ScreenUpdating = False
        If Range("A9").Value <> "" Then
                ws.ListObjects("PR11_P3_Tabell").Range.AutoFilter Field:=2, Criteria1:="*" & [A9] & "*", Operator:=xlFilterValues
            Else
                ws.ListObjects("PR11_P3_Tabell").Range.AutoFilter.ShowAllData
        End If
    Application.ScreenUpdating = True
    
End Sub

user_7011129 · Answer

you can achieve this using the AdvancedFilter instead. The VBA part is pretty simple (see below), and the tricky part is the criteria table setup.

You need to create a table with the number of rows and columns matching the number of columns in the Table you wanna filter. In my example, the tables has the columns Field1, Field2, ..., Field8 (the field names must match your own Table column names). So it has 8 columns, and 9 rows (1 for the header, and 8 for the criteria).

Each row of the criteria table represents one condition for your filter. So I inserted the formula ="*" & $A$1 & "*" (assuming this is the LinkedCell for the TextBox) for each column, but in a unique row for each column. That means it will filter when Field1 = "Fra" OR Field2="Fra", and so on.

With the criteria table done, you just need to call the AdvancedFilter method in VBA, like that:

Private Sub TextBox1_Change()

    ActiveSheet.ListObjects("PR11_P3_Tabell").Range.AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=Range("E2:L10")
        
End Sub

How can I implement search within more than one field in text box?

1 Answers