Macros on Protected Sheets

[Guest]

Hi,
I have a protected a Sheet and can't run my Macros.
For example, Ihave a button (unlocked), to which a Macro which filter or
sort a list is assigned. In the protection options I have allowed the users
to filter and sort and it still doesn'nt work. Have i missed to do something
? Can someone assist me on this matter ? Thanks in advance.

 

[Alan]

If you have a password, try wrapping the macro code with
Sheet("Your_Sheet"). Unprotect("Your_Password")
'Your Code
Sheet("Your_Sheet"). Protect("Your_Password")

Regards

 

[Guest]

To add to what Alan wrote, some code doesn't function in protected sheets.
For instance, if your code uses the CurrentRegion Property, the macro won't
function while the sheet is protected.

tj

 

[Norman Jones]

Hi Paul,

Try pasting the following in the ThisWorkbook module (right-click the Excel
icon to the left of the File menu, View Code):

Sub Workbook_open()
Dim sh As Worksheet

Set sh = Sheets("Sheet2") '<<===== CHANGE
sh.EnableAutoFilter = True
sh.Protect contents:=True, userInterfaceOnly:=True, Password := "drowssap"

End Sub

Change the sheet name and the password to suit your needs.


The Workbook_Open procedure is suggested because the userInterfaceOnly
setting is not persistent.

 

[Guest]

Thanks Alan for your reply,
Can you assist me with the Syntax (I am not VB literate !!)
I wrote it the folowwing:

Sub Newaction()
Sheet("Critical Path").Unprotect ("My Password")
' Newaction Macro
' Macro recorded 17/11/2004 by Clifford
'

'
Selection.AutoFilter Field:=1, Criteria1:="="
Sheet("Critical Path").Protect ("My Password")
End Sub

Can you tell me what's wrong ?
Thank you in advance

Paul

 

[Guest]

Alan ,
Thank you now it works but with "sheets" and not "Sheet",
Thanks again
Bye