Auto Filter help

kenjohnson_342

Board Regular
Joined
Feb 14, 2007
Messages
69
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Have you tried recording a macro while AutoFiltering for contains "CBQ"? That will give you some starter code. You could use the Worksheet_Change event procedure to trigger the AutoFilter.
 
Upvote 0
Also, if it makes a difference I password protect the worksheet so the rest of the staff can't screw up my pricing and formulas.
 
Upvote 0
ok so I recorded a macro and have the code below so far. Again with very limited experience with macros, how do I take the next step to have it reference the text in cell B1? Thanks for your help so far.

Sub TestLookup()
'
' TestLookup Macro
'

'
ActiveSheet.Range("$A$8:$L$8948").AutoFilter Field:=1, Criteria1:="=*cbq*" _
, Operator:=xlAnd
End Sub
 
Upvote 0
Try this in the module for the worksheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$B$1" Then Exit Sub
    Me.Unprotect
    Range("$A$8:$L$8948").AutoFilter Field:=1, Criteria1:="=*" & Target.Value & "*", Operator:=xlAnd
    Me.Protect
End Sub
 
Upvote 0
Thanks for your help Andrew but there's something I'm not getting.
It's not working for me. When I replace my text with yours my macro disappears completely. I've tried to play with it a little and just can't keep from getting errors.
As good as I feel I am with Excel, I really don't have the chance to learn VBA which is unfortunate.

I think maybe this is beyond what I"m going to be able to do. I think it would've been a really great feature to this file though.
 
Upvote 0
Right click the sheet tab and choose View Code. Copy my code from the Board and paste it into the window on the right. Return to you worksheet and try it out by changing what's in B1.
 
Upvote 0
**** you're awesome! Followed your instructions and works great.... if I unprotect my sheet. It runs just like I dreamt!

If my sheet is protected and i change the cell B1, it prompts me to enter a password though. How do get by this?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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