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

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

"Sho" wrote:

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

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

Stefi wrote:
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

"Sho" wrote:

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

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


€� ezt Ã*rta:

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

Stefi wrote:
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

"Sho" wrote:

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

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



Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

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:

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



Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

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

should be

Activesheet.Protect UserInterfaceOnly:=True

--
Regards,
Tom Ogilvy

" wrote:

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

Stefi wrote:
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

"Sho" wrote:

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