Section 6.4.Program with Passwords and Encryption

6.4. Program with Passwords and Encryption

You can set passwords and encryption options in code using the Workbook object's security members, such as the Password property and SetEncryptionProperties method. From a security standpoint, it doesn't make sense to hardcode passwords into Visual Basic macros. Instead, the Workbook object's security members are generally used in conjunction with User Forms to set passwords and encryption chosen by the user through a customized interface.

---

Note: You can enforce strong password rules by adding code to a template's Before_Save event.

---

6.4.1. How to do it

For instance, you might create a document template (.xlt) for secure documents that can only be saved using a password and encryption. Such a template might include a User Form to get the password (Figure 6-7).

Figure 6-7. Password User Form

The code for the User Form confirms that the password and confirm password text boxes match and allows the user to cancel the operation, as shown here:

    ' Public fields    Public Password As String, Encrypt As Boolean    Private Sub cmdCancel_Click(  )        Me.Hide        Password = ""    End Sub    Private Sub cmdSave_Click(  )        If txtPassword.Text <> txtConfirm.Text Then            MsgBox "Password and confirm password must match.", , _             "Confirm Error"        Else            Password = txtPassword.Text            Encrypt = chkEncrypt.Value            Me.Hide        End If    End Sub

Then, the Secure template includes a Workbook-level procedure to intercept the Save event. Whenever the user saves a document based on this template, the following code displays the Password user form and sets the workbook password and encryption options (points of note are shown in bold ):

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)        Dim fname As String        ' Exit if this is a template, not a workbook.         If ThisWorkbook.FileFormat = xlTemplate Then Exit Sub ' (1)        ' Cancel default operation.        Cancel = True        ' Get a password if one does not exist.         If Not ThisWorkbook.HasPassword Then ' (2)            frmPassword.Show             ThisWorkbook.Password = frmPassword.Password ' (3)            If frmPassword.Password = "" Then Exit Sub            If frmPassword.Encrypt Then                 ThisWorkbook.SetPasswordEncryptionOptions _            ' (4)                  "Microsoft RSA SChannel Cryptographic Provider", _                   "RC4", 128, True            End If        End If        ' Save the workbook by enabling the default action.         Cancel = False   ' (5)        ' Make sure the user form unloads.        Unload frmPassword    End Sub

6.4.2. How it works

The key points are:

1. Exit the procedure if saving a template. This allows you to save the template without a password.

2. Use the HasPassword property to determine if a password has already been set. You can't use the Password property to test this, since it always returns asterisks whether or not a password is set (for security reasons).

3. You can set a password by assigning the workbook's Password property or by using the SaveAs method. Using SaveAs in this case would call the Workbook_BeforeSave event procedure again, resulting in an unwanted recursion.

4. Use the SetEncryptionOptions method to choose the type of encryption and the length of the encryption key. This is the only way to set encryption options, since the PasswordEncryption properties are all read-only.

5. Set Cancel to False to allow Excel to complete the save operation. As mentioned in item 3, calling Save or SaveAs would result in unwanted recursion.

6.4.3. What about...

The Workbook object has over 200 members. To help navigate among the various ways to work with passwords from workbooks, Table 6-1 lists the Workbook security members. You can get Help on any of these members from the Visual Basic editor.

Table 6-1. Workbook security members

HasPassword	Password
PasswordEncryptionFileProperties	PasswordEncryptionKeyLength
SetPasswordEncryptionOptions	WritePassword
WriteReservedBy	PasswordEncryptionAlgorithm
PasswordEncryptionProvider	WriteReserved