Microsoft Excel —

Run Procedures on Protected Worksheets

Excel macros are a great way to save time and eliminate errors. However, sooner or later you might try to run your favorite Excel macro on a worksheet that has been protected, with or without a password, resulting in a runtime error. Avoid that problem with the following paragraphs.

If you have ever tried to run an Excel macro on a worksheet that's been protected, you know that as soon as the worksheet is encountered, your macro probably won't work and instead will display a runtime error.

One way to get around this is to use some code such as the following to unprotect and then protect your worksheet:

Sub MyMacro( )
Sheet1.Unprotect Password:="Secret"
'YOUR CODE
Sheet1.Protect Password:="Secret"
End Sub

As you can see, the code unprotects Sheet1 with the password Secret, runs the code, and then password-protects it again. This will work, but it has a number of drawbacks. For one, the code could bug out and stop before it encounters the Sheet1.Protect Password:="Secret" line of code. This, of course, would leave your worksheet fully unprotected. Another drawback is that you will need similar code for all macros and all worksheets.

Another way to avoid this problem is to use UserInterFaceOnly, which is an optional argument of the Protect method that you can set to True. (The default is False.) By setting this argument to True, Excel will allow all Excel VBA macros to run on the worksheets that are protected with or without a password.

However, if you use the Protect method with the UserInterfaceOnly argument set to True on a worksheet and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To set the UserInterfaceOnly argument back to True after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.

To avoid this hassle, you need to use the Workbook_Open event, which is fired as soon as the workbook is opened. Because this is an event of the Workbook object ThisWorkbook, you must place the following code in the private module of ThisWorkbook. To do this in Windows versions of Excel, right-click the Excel icon and select View Code. On Macs, open the Workbook object from the Projects window of the VBE. Then enter the following:

Private Sub Workbook_Open( )
'If you have different passwords
'for each worksheet.
Sheets(1).Protect Password:="Secret", UserInterFaceOnly:=True
Sheets(2).Protect Password:="Carrot", UserInterFaceOnly:=True
'Repeat as needed.
End Sub

The preceding code is good if each worksheet on which you want your macros to operate has a different password, or if you do not want to protect all worksheets. You can set the UserInterfaceOnly argument to True without having to unprotect first.

If you want to set the UserInterfaceOnly argument to True on all worksheets and they have the same password, you can use the following code, which must be placed in the same place as the preceding code:

Private Sub Workbook_Open( )
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        wSheet.Protect Password:="Secret", _
                    UserInterFaceOnly:=True
    Next wSheet
End Sub

Now, each time you open the workbook, the code will run and will set the UserInterfaceOnly property to True, allowing your macros to operate while still preventing any user changes.