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.
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.