VB Script for Autofilter

[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

 

[Guest]

For Each ws In Worksheets

ws.Protect Password = "password", AllowFiltering:=True
Next ws

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

 

[james.billy]

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

 

[Guest]

Yes, sorry, I forgot to add that it works only in Excel2003!
Stefi


„[email protected]†ezt írta:

 

[Les Stout]

Hi Stefi,
see also in VB help under "EnableAutofilter Property"



Les Stout

 

[Guest]

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:

 

[Guest]

Just a slight correction:
Activesheet.Protect UserInterface:=True

should be

Activesheet.Protect UserInterfaceOnly:=True