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.


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.

Jamani Arsalan


You may also like

April 23, 2014

A VBA Coding Manifesto (Part 1)

May 3, 2014

A VBA Coding Manitesto (Part 2)

December 22, 2013

Excel Holiday Card
  • 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.

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

  • The code sounds perfect to me, so I really want to use it.
    But it’s not working for me 🙁

    I’m getting a error on the keyword Me.

    So I have two questions;
    1. Where do I put the code (in a specific Worksheet, ThisWorkbook or in a Module or something)?
    2. Why do I get this error on the keyword Me and how can I fix it?

    Thanx for the answer and the support
    a happy Excel user

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!