Page 168 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

Sub SetPassword()
Dim strPassword1 As String
Dim strPassword2 As String
strPassword1 = InputBox ("Type a password for the workbook.")
strPassword2 = InputBox ("Re-type the password.")
If strPassword1 <> strPassword2 Then
Chapter 7
MsgBox ("The passwords don't match. Please try again.")
Else
ThisWorkbook.Password = strPassword1
MsgBox ("The password is set.")
End If
End Sub

Important You probably noticed that the SetPassword procedure required the user to
type in the same password twice to set the password for the workbook. When you build rou;
tines that restrict access to data, you should always make sure to verify the password is
what the user intended it to be. Remember, if the data is important enough to protect, it’s
worth it to add extra measures to safeguard the password.

Another way you can protect a workbook is to prevent users from saving changes unless the
user knows the password used to protect the workbook. When a workbook is write-pro-
tected, the WriteReserved property is set to True. Writing code to change a write-protect pass-
word on the fly is messy, because the user would need to type in the current password and
then set a new one, so the following routine checks to make sure the active workbook is not
write-protected before allowing the user to set a password users must enter before being
allowed to save changes:

Sub SetWritePassword()
Dim strPassword1 As String
Dim strPassword2 As String
strPassword1 = InputBox ("Type a password for changes to be saved.")
strPassword2 = InputBox ("Re-type the password.")
If strPassword1 <> strPassword2 Then
MsgBox ("The passwords don't match. Please try again.")
Else
If ActiveWorkbook.WriteReserved = False Then
ActiveWorkbook.WritePassword = strPassword1
Else MsgBox ("The workbook is already write protected.")
End If
MsgBox ("The password is set.")
End If
End Sub

Protecting Workbooks from Changes

When you use the WritePassword method to protect your workbook, you’re requiring users to
know a password so they can open the workbook. Of course, once they have opened the work-
book, they can make any changes they like to it. If you want to add a second layer of protection,
you can do so by setting a separate password that must be entered before users would be able to
make any changes to your workbook. By using the Protect method, you can prevent users from
142