VBA for a type as you search text box on a multi column table

[Liverlee]

Hello All.

Hoping you can help me out with a VBA code. I've got a table called 'data' from a3:i1000. A3 to I3 is the header row. I've added a text box linked to cell A1. When a user enters any text value in the text box, I'm hoping the table will auto-filter to rows with that specific keyword match. So for example, if someone typed yes into the text box - then rows 4/5/7/8 would show and the other rows would be filtered out. And then when the text is cleared from the text box, all the other results reappear.
Can anyone provide a code or point me where I can find out how to do this?

 

[Georgiboy]

Have you looked at slicers?

If you select the table and go to 'Table Design' in the ribbon - you will find the option to 'Insert Slicer' - it seems like they will do what you need.

 

[Liverlee]

not after slicers, too many variables. Its a really big spreadsheet thousands of rows, lots of columns, I need a better solution than one user putting on a filter and then the next user coming along and going through each column to find where the filter is to unfilter to then add another filter for what they are looking for.

So was thinking a text box search and filter at the top of the page in cell a1 where it's visible to all users whats been searched for and deleting that text in the box will return all rows and columns

 

[Akuini]

So was thinking a text box search

Do you want to search in only 1 specific column or in any column of your choice?

 

[Georgiboy]

I need a better solution than one user putting on a filter and then the next user coming along and going through each column to find where the filter is to unfilter to then add another filter for what they are looking for.

Not sure what this means as the filter can be removed from within the slicer itself, you would not have to look for it in the headers. If you are not after a slicer though thats fair enough as i do get the "too many variables" part.

 

[Liverlee]

Do you want to search in only 1 specific column or in any column of your choice?

any column that's the bit i'm getting stuck at

this works for just column A but I can't get it to work for the other columns

Private Sub TextBox1_Change()

Dim filterValue As String

filterValue = TextBox1.Value

With Range("A3:I1003")

.AutoFilter Field:=1, Criteria1:="=*" & filterValue & "*"

End With

End Sub

 

[Akuini]

any column that's the bit i'm getting stuck at

this works for just column A but I can't get it to work for the other columns

Are you saying you need to filter just 1 column & using1 criteria at a time but which column is a variable?

 

[RudRud]

Book4
	A	B	C	D	E	F	G	H	I
1
2
3	Project	Project St	Project Owner	Project Scoope	Project Start	Implementation Date	Update	Update Details	Updated
4	A1	ONE	Name 1	SCOPE 1	1/1/2023		Yes	Updates notes 1	6/2/2023
5	AAA	TWO	Name 2	SCOPE 2	1/1/2023		Yes	Updates notes 2	6/3/2023
6	CCC	Three	Name 3	SCOPE 3	1/1/2023		No	Updates notes 3	6/4/2023
7	A1	ONE	Name 1	SCOPE 1	1/1/2023		Yes	Updates notes 1	6/2/2023
Sheet1

Cell Formulas
Range	Formula
I4,I7	I4	=TODAY()
I5	I5	=TODAY()+1
I6	I6	=TODAY()+2

While waiting for other gurus, will share my thought here.

After input textbox need to press f4 to run the code

Option Compare Text

Private Sub TextBox1_DropButtonClick()
Dim filterValue As String
On Error Resume Next

filterValue = TextBox1.Value

With Range("A3:I" & Cells(Rows.Count, "A").End(xlUp).Row)

    k = .Find(filterValue, lookat:=xlWhole).Column
    If CInt(k) = 0 Then
        Range("a3").AutoFilter
    Else
        .AutoFilter Field:=k, Criteria1:="=*" & filterValue & "*"
    End If

End With

End Sub

 

[Liverlee]

Are you saying you need to filter just 1 column & using1 criteria at a time but which column is a variable?

Hi -the data set isn't the best example.

But the word "Akuini" could appear in cell a3 or b17, or c58, or d900, or e1000 etc

So ultimately i'd like the textbox to filter out all rows except row 3, 17, 58, 900, 1000 cos they have the "Akuini" word matches

 

[Liverlee]

While waiting for other gurus, will share my thought here.

After input textbox need to press f4 to run the code

Option Compare Text

Private Sub TextBox1_DropButtonClick()
Dim filterValue As String
On Error Resume Next

filterValue = TextBox1.Value

With Range("A3:I" & Cells(Rows.Count, "A").End(xlUp).Row)

    k = .Find(filterValue, lookat:=xlWhole).Column
    If CInt(k) = 0 Then
        Range("a3").AutoFilter
    Else
        .AutoFilter Field:=k, Criteria1:="=*" & filterValue & "*"
    End If

End With

End Sub

that's the closest i'm come to solving this so far only issue is when i delete the text i still have to manually unfilter but many many thanks for helping