VB Script for Autofilter


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
 
Ad

Advertisements

G

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
 
J

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
 
G

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:
 
Ad

Advertisements

G

Guest

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

should be

Activesheet.Protect UserInterfaceOnly:=True
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top