Protecting a Sheet

[ERed1]

Hi all,

I have a spreadsheet with macros that I wrote. I have to give it to some colleagues and need to protect the sheet. When I protect it normally it makes the code not work. Has anyone protected a macro enabled worksheet?

Thanks

 

[sykes]

Write some lines into your code, to un-protect the sheet, then re-protect, after the code's run:

Sub unprotect_sheet()
Me.Unprotect "password"
' All your code here...
Me.Protect "password"
End Sub

Obviously change the password to the one you've used, and if you don't want your users having a sneaky peek into your code, to find the hard-coded sheet's password, then password-protect your project, too.

The "Me" keyword will only work if you put the code into the actual sheet's code module. If the code's anywhere else, you'll need to refer to the sheet longhand "Sheets("Sheet1") or "Activesheet" etc.

 

[Steve_]

Hi all,

I have a spreadsheet with macros that I wrote. I have to give it to some colleagues and need to protect the sheet. When I protect it normally it makes the code not work. Has anyone protected a macro enabled worksheet?

Thanks

Is your code going to affect the protected sheet? If so, you will need to programmatically unprotect it prior to your code running and then programmatically re-protect it after code execution.

 

[ERed1]

That makes sense. Thank you for the code to do it, it worked.

 

[sykes]

You're welcome. Thanks for the feedback.