VB Script for Autofilter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following script where I want to be able to password protect a
sheet but still allow Autofiltering to work. It protects the sheet but you
cannot Autofilter. Any ideas where I have gone wrong?

Sub Lock()

For Each ws In Worksheets
AllowFiltering:=True
ws.Protect Password = "password"
Next ws

End Sub
 
For Each ws In Worksheets
ws.Protect Password = "password", AllowFiltering:=True
Next ws
Click to expand...

You can see this if you record your protect action as a macro:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFiltering:=True

Regards,
Stefi
 
Stefi's answer would work in later versions of Excel however in older
versions of excel you do not get the AllowFiltering property of the
activesheet (Im using Excel 2000 and the property does not exist
there), instead you need to enable the autofilter then protect the
userinterface only, something like:

Application.EnableAutofilter = True
Activesheet.Protect UserInterface:=True

The only problem with this is that the userinterface property gets
reset each time you open and close Excel, therefore you would need to
add the code to the workbook open event.

James
 
Thanks Les, one can always learn something! Does it help Sho? I'm afraid it
applies also to only XL2003!
Stefi


„Les Stout†ezt írta:
 
Just a slight correction:
Activesheet.Protect UserInterface:=True

should be

Activesheet.Protect UserInterfaceOnly:=True
 
Back
Top