Bottom Line: Learn how to run a macro on a protected sheet while maintaining security in Excel.

Skill Level: Intermediate

---

In Excel, protecting a sheet can prevent unauthorized changes, but it can also block macros from running. However, you can enable macros to function on protected sheets with a few simple tweaks.

Allow Macros to Run on a Protected SheetBy temporarily unprotecting the sheet within your macro, you can make changes and then protect it again automatically.

Steps:

1. Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11.
2. Write your macro code to unprotect the sheet, perform the desired action, and re-protect the sheet:

**Sub MyMacro() Sheets("Sheet1").Unprotect Password:="yourpassword" ' Your macro code goes here Sheets("Sheet1").Protect Password:="yourpassword" End Sub**

1. Press **F5** to run the macro.

Note: If the sheet is unprotected, the macro will still work. The Unprotect command will simply be ignored, and the rest of the macro will run as usual. You don’t need separate versions of the macro for protected and unprotected sheets. However, keep in mind that this macro will always leave the sheet protected by the end, regardless of its starting state.

Alternative: Run Macros Without Unprotecting the SheetIf you want to allow your macro to make changes to a protected sheet without unprotecting it each time, you can use the UserInterfaceOnly option in your code. This setting allows macros to make changes to the sheet, but keeps the protection in place for the user.

This means:

• Users cannot make changes to the protected sheet.
• Macros can run freely without needing to unprotect or re-protect the sheet.

Use the following line of code in your macro:

**Sheets("Sheet1").Protect Password:="yourpassword", UserInterfaceOnly:=True**

This tells Excel to protect the sheet for users but allows the macro to edit the sheet.

Note: While this may seem like a simpler solution than unprotecting and re-protecting, there is a disadvantage to this method. This setting only works for the duration of your session (until you close the workbook). You would need to rerun this protection command each time the workbook is opened.

WorkaroundOne workaround for this limitation is to run the protection macro every time the workbook opens. This can be done automatically with the Workbook_Open event, ensuring that the UserInterfaceOnly setting is applied each time the workbook is launched.

Here’s how to set it up:

1. Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11.
2. In the Project Explorer window, find your workbook, and double-click on ThisWorkbook.
3. Add the following code to the Workbook_Open event:

Private Sub Workbook_Open()
Sheets(“Sheet1″).Protect Password:=”yourpassword”, UserInterfaceOnly:=True
End Sub

This will automatically apply the protection with the UserInterfaceOnly setting every time the workbook is opened, allowing your macros to continue working without unprotecting the sheet. Users will still be restricted from making changes, maintaining the sheet’s security.

This method ensures you won’t have to remember to reapply the protection each time, streamlining your workflow while keeping your sheet protected.

ConclusionRunning a macro on a protected sheet is simple with the right approach and can be done without needing to manually unprotect or re-protect the sheet.

We'd love to hear your feedback or answer any questions you have. You can reach us by leaving a comment below!

Link to post: Run a Macro on a Protected Sheet in Excel