Running macros on protected worksheets

Posted by andre delanchy on July 25, 2001 12:44 PM

I have a worksheet that has 2 very simple macros that run the Autofilter. However, if I protect the worksheet and use a password, Excel says 'you cannot use this command on a protected sheet'.
Would you know if there is a way around that. I need to keep the workbook protected so that other users cannot make changes to the worksheet, but are able to use the AutoFilter option (by clicking on the AutoFilter arrow).

Macro 1
Sub ShowLog()
Selection.AutoFilter Field:=1
End Sub

Macro 2
Sub HideLog()
Selection.AutoFilter Field:=1, Criteria1:="show"
Range("D5").Select
End Sub

I hope you can help with this query.
Regards
Andre

Posted by Susan on July 25, 2001 1:02 PM

You need to add a line of code at the beginning and end of your macros to turn the protection off and on again.

To Unprotect:
Activesheet.Unprotect ("My_Password")

and to Re-Protect:
Activesheet.Protect Password:="My_Password"

You can get much trickier if necessary -- For instance, I keep my passwords in a separate macro in a different module and use a variable to pass them back and forth, but this shouldn't be necessary unless you have users who like to try to break your code!

Posted by Travis on July 25, 2001 1:05 PM

This will unprotect your sheet before running the macro and then will protect it back before ending. You may need to protect your macro from being viewed so no one can find your password. Goto your macro module in VBA and right click. Goto VBA Project Properties and protect your macro.

Macro 1
Sub ShowLog()
ActiveSheet.UNPROTECT Password:="Your password here"
Selection.AutoFilter Field:=1
ActiveSheet.PROTECT Password:="Your password here", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub