Page 169 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

changing the workbook by adding worksheets, deleting worksheets, displaying hidden work-
sheets (that is, changing the workbook’s structure), and also prevent users from changing the
Chapter 7
sizes or positions of the windows in your workbook (changing the workbook’s windows).

Note When you use the Protect method attached to a Workbook object, the protections
you set will apply to the entire workbook.

The Workbook object’s Protect method has the following syntax:
Protect[Password], [Structure], [Windows]

No need for a table this time! The Password parameter is the password (that’s required), but
the Structure and Windows parameters are optional. By default they’re set to False, but if you
set them to True, as in the following code example, then the structure and windows layout of
the workbook will be protected.

Sub SetProtection()
Dim strPassword1 As String
Dim strPassword2 As String
'First, check to be sure the workbook isn't protected already.
If (ActiveWorkbook.ProtectStructure <> True And _
ActiveWorkbook.ProtectWindows <> True) Then
strPassword1 = InputBox("Type a password to protect the workbook.")
strPassword2 = InputBox("Re-type the password.")
'Verify the passwords are the same
If strPassword1 <> strPassword2 Then
MsgBox ("The passwords don't match. Please try again.")
Else
ActiveWorkbook.Protect Password:=strPassword1, Structure:=True, _
Windows:=True
MsgBox ("The password is set.")
End If
'Back in the part of the routine that checks for protection.
Else
MsgBox ("The workbook is already protected.")
End If
End Sub

Inside Out

When Is an Error Not an Error?
It’s interesting to note that attempts to protect a workbook that’s already protected don’t
generate an error message: they just fail. An earlier version of the Sub SetProtection pro;
cedure just shown checked for errors and, rather than stopping when the procedure was
run against a protected workbook, the routine blithely continued to the end without notifying
the user that anything was wrong. The only reliable way to check for protection is to query
the ProtectStructure and ProtectWindows properties.

143