Excel 2003 Programming: A Developer's Notebook

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.

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 ...