Filter worksheet based on Text Box

mari_hitz

Board Regular
Joined
Jan 25, 2011
Messages
101
Hi everyone,

I have a question: I have a text box located on sheet1 and on sheet2 I have a table with several data. I would like to filter the first field of the table based on the info that an user enters to the text box. Do you know a code that can do that?

Thanks!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Assuming the field is in column 1 and assuming there's only one text box, right-click on the sheet tab, select View Code, enter this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Columns(1).AutoFilter Field:=1, Criteria1:=ActiveSheet.TextBoxes(1).Characters.Text
End Sub

The code will be fired when you click outside the text box (and whenever you click anywhere, but the code runs fast and is just a 1-liner!)
 
Upvote 0
Assuming the field is in column 1 and assuming there's only one text box, right-click on the sheet tab, select View Code, enter this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Columns(1).AutoFilter Field:=1, Criteria1:=ActiveSheet.TextBoxes(1).Characters.Text
End Sub

The code will be fired when you click outside the text box (and whenever you click anywhere, but the code runs fast and is just a 1-liner!)

Hi BobUmlas,

Thanks for your reply but your code does not work :S As per my understanding you are referencing to the text box as it was in the same sheet where the table with data is, and my table with data is on sheet2 and the text box is on sheet 1. Do you know how to reference that? Thanks
 
Upvote 0
On the sheet with the filter:
Private Sub Worksheet_Activate()
ActiveSheet.Columns(1).AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").TextBoxes(1).Characters.Text
End Sub

Note that now this is in the Worksheet_Activate event, not the SelectionChange event.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top