How To Run Macros In Protected Worksheets - Excel VBA Tips - Excel TV
Excel TV
Share The LOVE

How To Run Macros In Protected Worksheets – Excel VBA Tips

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:

  1. Unprotecting the sheet,
  2. Running the code we want to run, and
  3. 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.

Picture1

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.

  • Joe Kizinski says:

    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.

  • >