Creating a Macro to Filter based on an Entered Text

[Chris Macro]

Hi, I am pretty new to macros and have been using the record feature a lot. I have been looking for a way to have a searchbox (which is really just a textbox) that when text is entered in and I click on my macro button that it will filter only what was entered in the searchbox. For example if I have a list of products and suppliers A, B, & C. I want to be able to type in "A" and click the macro button and have it filter to just the "A" suppliers. Thank you for your help!

 

[Chris Macro]

This is the code I currently have:


ActiveSheet.Range("$A$6:$AF$643").AutoFilter Field:=5, Criteria1:= _
"=*A*", Operator:=xlAnd

I believe the blue highlighted text is what needs to be modified. I want it to equal the text entered in "textbox 38"

 

[Andrew Poulsom]

Assuming your TextBox is from the Control Toolbox:

    With ActiveSheet
        .Range("$A$6:$AF$643").AutoFilter Field:=5, Criteria1:= _
            "=*" & .OLEObjects("TextBox38").Object.Text & "*", Operator:=xlAnd
    End With

 

[Chris Macro]

Thanks for your help! Here is what I have in my code. I am getting an error of "expected End Sub". When I put in "End Sub" it gives me the error "Expected End with"

Also, I am just using a regular textbox. For some reason I can not insert a control textbox. Excel will not let me click on the icon.


Code:

Sub Search_Supplier()
With ActiveSheet
.Range("$A$6:$AF$643").AutoFilter Field:=5, Criteria1:= _
"=*" & .OLEObjects("TextBox38").Object.Text & "*", Operator:=xlAnd
End With

 

[Andrew Poulsom]

For a TextBox from the Drawing Toolbar:

Sub TTT()
    With ActiveSheet
        .Range("$A$6:$AF$643").AutoFilter Field:=5, Criteria1:= _
            "=*" & .Shapes("Text Box 38").TextFrame.Characters.Text & "*", Operator:=xlAnd
    End With
End Sub

Change the name to suit.

 

[Chris Macro]

Andrew that worked out great! The only thing I had to change in the code was "text box 38" needed to be "textbox 38". But other than that it works perfectly!! Thank you so much for your time!