Protecting a Sheet

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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

Well-known Member
Joined
May 1, 2002
Messages
1,561
Write some lines into your code, to un-protect the sheet, then re-protect, after the code's run:
Code:
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.
 
Last edited:

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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

Board Regular
Joined
Jun 26, 2019
Messages
104
That makes sense. Thank you for the code to do it, it worked.
 

Forum statistics

Threads
1,082,604
Messages
5,366,581
Members
400,904
Latest member
ndaines meriabi

Some videos you may like

This Week's Hot Topics

Top