Microsoft Office Tutorials and References
In Depth Information
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
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 Tr ue . Writing code to change a write-protect
password 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
workbook, 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
Search JabSto ::




Custom Search