Microsoft Office Tutorials and References
In Depth Information
changing the workbook by adding worksheets, deleting worksheets, displaying hidden
worksheets (that is, changing the workbook’s structure ), and also prevent users from changing the
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 Wo rkbook 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 Tr ue , as in the following code example, then the structure and windows layout of
the workbook will be protected.
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.")
ActiveWorkbook.Protect Password:=strPassword1, Structure:=True, _
MsgBox ("The password is set.")
'Back in the part of the routine that checks for protection.
MsgBox ("The workbook is already protected.")
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.