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 Sheet
By temporarily unprotecting the sheet within your macro, you can make changes and then protect it again automatically.
Steps:
- Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11.
- 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
- 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 Sheet
If 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.
Workaround
One 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:
- Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11.
- In the Project Explorer window, find your workbook, and double-click on ThisWorkbook.
- 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.
Conclusion
Running 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!
Your first example of turning off protection, running the macro, and restoring protection requires a blanket error trapping routine to assure protection is reapplied if something goes wrong causing the macro to die during execution.
UserInterfaceOnly:=True sounds like the obvious answer, but I’ve long since abandoned it as it is unreliable. Clients had problems with my workbooks, and I had to go back to unlocking for as short a time as possible before relocking.
Hi!
This is almost exactly what I was looking for.
I have a financial workbook I’m creating for myself and some friends (who have no working knowledge of Excel) and would like to set it up so they can only select or tab to their data entry cells allowing the rest of the worksheet (all the formulas) to be protected.
The only time a macro will be used in this workbook will be at the end of the year when you reset the data entry worksheet and save the collected data.
So, my question is, can my current macro have the unlocking VBA code entered at the start of the macro and the locking VBA code entered at the end of the macro so the user is still only “pressing” one button?
Thanks for looking at this for me.
Mitch
Hi guys, I was doing this activity almost with the same approach, but I have used variables, like this:
1. this procedure is to protect the sheet
Option Explicit
Sub Secure_Sheets()
Dim sheet As Worksheet
Dim secure_code As String
secure_code = “VBA123”
For Each sheet In Worksheets
sheet.Protect Password:=secure_code
Next sheet
End Sub
2. this procedure is to unprotect the sheet
Option Explicit
Sub Unprotect_Sheets()
Dim sheet As Worksheet
Dim secure_code As String
secure_code = “VBA123”
For Each sheet In Worksheets
sheet.Unprotect Password:=secure_code
Next sheet
End Sub
The command UserInterfaceOnly:=True/False is new for me but I am sure that I will use it in my macros. Have a nice Excel Year 2025…
Thanks for the 3 options to protect worksheets. So, I used this macro and it works. Sheets(“Sheet1″).Protect Password:=”yourpassword”, UserInterfaceOnly:=True
How can I use that same for multiple worksheets? I used it on this worksheet and it works. If I add another worksheet, it stops.
Sheets(“Prognosis”).Protect Password:=”123″, UserInterfaceOnly:=True
Is there something in the formula I should modify to add another worksheet?
Love it! Thank you!