autofilter & protection

dantb

Active Member
Joined
Mar 20, 2002
Messages
358
I was wondering if there was anyway to protect a sheet, and still be able to use the autofilter function. Thanks Dan
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
not sure if this is suitable, but you could protect your sheet and use another sheet with =Sheet1!$A$1 refs for the autofilter. you could also use an array (eg. =Sheet1!$A$1:$A$20 & ctrl+shift+enter) for the second sheet, which you can still autofilter but the user can't for instance just change one cell in the array.
 
Upvote 0
I have a vba that will let you use the advance filter option, but cant seam to be able to make the autofilter work.

Private Sub Workbook_Open()
Application.CommandBars("Worksheet Menu Bar").Controls _
("Data").Controls("filter").Enabled = True
End Sub
 
Upvote 0
will the user be looking for one set thing at a time.
eg. "SMITH"
or will they be looking for "SM......"
or blanks?
 
Upvote 0
I am filtering a query which is a large dbase, there are 8 different collums and many ways to search for data. I noteced the Advanced filter works with the code above, I didnt know if there was a way to get to the sub-menu Item. thx
 
Upvote 0
I have a vba that will let you use the advance filter option, but cant seam to be able to make the autofilter work.

advanced filter is still enabled with sheet protection but its requirements are different to autofilter and if your code doesn't take this into account (eg the need to enter a criteria range) maybe that's why it doesn' work. i have never used advanced filter and know nothing about vba so sorry can't advise anything.
 
Upvote 0
SOLUTION

With Worksheets("myworksheet")
.EnableSelection = xlUnlockedCells
.Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True
End With

It' taken me months to get this
 
Upvote 0
thanks Qroozn, I put it under my sheet1 general, but still only have the advanced filter option. Is it in the correct place ?
 
Upvote 0
is your autofilter saved on your worksheet?
the macro should stop you being able to select anything except the autofilters already on the worksheet.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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