Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 93 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 hack.

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.