How can I find out in a script if the sheet is protected?

[chx]

Hi,

I have a button in my sheet that inserts a couple of rows etc.
What I want to do is either hiding it when the sheet is protected, or show a message to the user when he clicks the button.
Right now, when I click the button and the sheet is protected I get an error that tells med it's not ok to do so, but it's ugly (error code 1004).

 

[Andrew Poulsom]

Add this as the first line of your CommandButton_Click procedure:

If ActiveSheet.ProtectContents Then Exit Sub

Clicking the button will then do nothing and there will be no erro messages.

 

[chx]

Thanks a lot! I'm happy again :wink:

 

[Joe Was]

If a range is in or part of a protected region, macro code will fail. To run a macro with protected ranges you must wrap your macro code in unprotect-protect code. Like:

In the sheet module put:

Private Sub Worksheet_Change(ByVal Target As Range)
'
' Macro by Joseph S. Was
'
Application.EnableEvents = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
Application.EnableEvents = True
End Sub

Then in a module add the unlock code and attach your code, if any, before the End Sub.

Note: You can run the un-lock code from a Form-Button or use a hot-key with Macros-Macro-select macro-Options-select your key.


Sub myUnLock()
'This code will unprotect the sheet.
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect ("admin")

'Add optional code here!

Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Then run your code out of the unlock sub, the first macro will password lock the code behind you automatically. You can make the password anything you want. To help hide your password you can password lock your macros form the VBA editor pull-down menu!

Note: this uses hard coded password protection which will decrease the security of the protection. But then again a password does not really deter someone from getting in if they want to, like a door lock does not stop some one from getting in a window, but does stop the causal passer-by from coming in.

To add a "MsgBox" to your macro and not use the code above, that is you do not want the user to run your macro through your button when the sheet is protected, you have two main ways of doing this:

You can add an: "On Error GoTo myErr" flag at the start of your code and at the bottom of your code put:

End
myErr:
MsgBox "This button has been deactivated!"
End Sub

Or record a two macros: One you right-click delete your form button off the sheet. Then add that code to the bottom of your protect code. Then record another macro, replacing your form button. Add that code to the begining of your unprotect macro.

In this way when you run the code to umprotect your macro button will be created on your sheet. And, when you run your protect code the button will be removed from your sheet. JSW