Auto Filter help

[kenjohnson_342]

What I got: worksheet with a bunch of part numbers, descriptions and prices etc that less Excel-Saavy coworkers use all day long.
With 10 columns wide by 10,000 rows deep we use it to quote customers over the phone. I've tried over and over to explain how the "FIND" function works but no go.
I then created a VLOOKUP scenario where they could input the part number to get the info they need, however it has to be an exact match. Sometimes there are suffexes to part numbers that make that difficult.

Wet dream...
I would love to create an input box or a cell, where it could trigger the auto-filter function.
So user enters "CBQ" into the box or cell and it would auto-filter the part column for instances where it contains "CBQ". That would capture all part numbers with or without suffexes.

I have very little experience with programming and writing macros and control boxes. So a complicated vba option won't work well for me. Anybody have any good suggestions for me?

Thanks, Ken.

 

[Andrew Poulsom]

You can include the password as an argument:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$B$1" Then Exit Sub
    Me.Unprotect Password:="yourpassword"
    Range("$A$8:$L$8948").AutoFilter Field:=1, Criteria1:="=*" & Target.Value & "*", Operator:=xlAnd
    Me.Protect Password:="yourpassword"
End Sub

Change yourpassword to suit.

 

[kenjohnson_342]

Perfect! Absolutely perfect.

Thank you very much for your help. I love this program and I know there is so much potential with VBA.

Thanks again, Ken.