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