A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
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.
One way to work around this issue is to build a certain backdoor in our code. This backdoor is basically the following three steps:
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.
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.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide