We know that human error often messes up complicated calculations or elegant models set up in Excel. For this reason, many of us protect worksheets using “Me.Protect” within our macros. But there is a drawback in doing that. Protecting sheets disables macros from running!!
Worry no more! Excel MVP Jordan Goldmeier aka Option Explicit is here to address this issue for us.
Well, Let’s Begin
1 – A Risky Way
One way to work around this issue is to build a certain backdoor in our code. This backdoor is basically the following three steps:
- Unprotecting the sheet,
- Running the code we want to run, and
- Protecting the sheet again.
It appears to be a very smart way to tackle the issue, but there is a risk. If the code breaks down into an error for some reason, the final step would not be executed. Which means that the worksheet will not be protected again. So, it’s a risky move.
2 – The Better Way
While protecting a worksheet within our macros, we use the command “Me.Protect”. This enables worksheet protection and disables all macros that follow from running. Also, an error shows up saying the same. But there is an option within this ‘Protect’ method that allows only the user interface to be locked.
If the command “Me.Protect UserInterfaceOnly := True” is supplied, this is exactly what happens. Only the user interface is protected. And… you guessed it right, macros that follow would be executed as they are.
What’s next?
It’s time to try it out yourself. And, most importantly, share this with your colleagues. It’s time to tell them that we are here to help, one problem at a time.
- SSSVEDA DAY 7 – Every Team Needs Someone Who Understands Data - February 18, 2018
- SSSVEDA DAY 5 – When Data Analysis is Wrong - October 31, 2017
- SSSVEDA DAY 4 – Sharing the Excel Knowledge - July 18, 2017
I was successful using Me. Protect on one sheet. I replicated the code for another sheet in the same workbook, but it it still gives me the Run-time error – cannot use this command on protected sheet. Any help would be appreciated.
The sheet has to be unprotected before you use it (*I think*).
Thanks Jordan, that really works. Great post.
I have one more related problem you might be able to help me with. I tried this on a protected “shared” workbook. Excel does not like the combination of protected and shared, it returns a Run time error 1004 – Method “Protect of object Worksheet failed.
Thanks
Garry
Thanks a lot…its work for me…its very worth for me…