Password/Protect related procedures in class Workbook (Excel VBA)

This page presents procedures from class Workbook related to the theme Password/Protect: HasPassword, Password, PasswordEncryptionAlgorithm, PasswordEncryptionFileProperties, PasswordEncryptionKeyLength, PasswordEncryptionProvider, Protect, SetPasswordEncryptionOptions, Unprotect, WritePassword, WriteReserved and WriteReservedBy

HasPassword

True if the workbook has a protection password.

You can assign a protection password to a workbook by using the SaveAs method.

Dim booHasPassword As Boolean
booHasPassword = ActiveWorkbook.HasPassword

Password

Returns or sets the password that must be supplied to open the specified workbook.

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements.

ActiveWorkbook.Password =

PasswordEncryptionAlgorithm

Returns a String indicating the algorithm that Microsoft Excel uses to encrypt passwords for the specified workbook.

Use the SetPasswordEncryptionOptions method to specify whether Excel encrypts file properties for password-protected workbooks.

ActiveWorkbook.SetPasswordEncryptionOptions PasswordEncryptionProvider:="Microsoft RSA SChannel Cryptographic Provider", 
 PasswordEncryptionAlgorithm:="RC4", PasswordEncryptionKeyLength:=56, PasswordEncryptionFileProperties:=True

PasswordEncryptionFileProperties

True if Microsoft Excel encrypts file properties for the specified password-protected workbook.

Use the SetPasswordEncryptionOptions method to specify whether Excel encrypts file properties for the specified password-protected workbook.

Dim booPasswordEncryptionFileProperties As Boolean
booPasswordEncryptionFileProperties = ActiveWorkbook.PasswordEncryptionFileProperties

PasswordEncryptionKeyLength

Returns a Long indicating the key length of the algorithm that Microsoft Excel uses when encrypting passwords for the specified workbook.

Use the SetPasswordEncryptionOptions method to specify whether Excel encrypts file properties for the specified password-protected workbook.

Dim lngPasswordEncryptionKeyLength As Long
lngPasswordEncryptionKeyLength = ActiveWorkbook.PasswordEncryptionKeyLength

PasswordEncryptionProvider

Returns a String specifying the name of the algorithm encryption provider that Microsoft Excel uses when encrypting passwords for the specified workbook.

Dim strPasswordEncryptionProvider As String
strPasswordEncryptionProvider = ActiveWorkbook.PasswordEncryptionProvider

Protect

Protects a workbook so that it cannot be modified.

Protect (Password, Structure, Windows)

ActiveWorkbook.Protect

Arguments

The following arguments are optional

Password (String) - A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook. Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

Structure (Boolean) - True to protect the structure of the workbook (the relative position of the sheets). The default value is False.

Windows (Windows) - True to protect the workbook windows. If this argument is omitted, the windows aren't protected.

SetPasswordEncryptionOptions

Sets the options for encrypting workbooks by using passwords.

The PasswordEncryptionProvider, PasswordEncryptionAlgorithm, and PasswordEncryptionKeyLength arguments are not independent of each other. A selected encryption provider limits the set of algorithms and key length that can be chosen. For the PasswordEncryptionKeyLength argument, there is no inherent limit on the range of the key length. The range is determined by the Cryptographic Service Provider, which also determines the cryptographic algorithm.

SetPasswordEncryptionOptions (PasswordEncryptionProvider, PasswordEncryptionAlgorithm, PasswordEncryptionKeyLength, PasswordEncryptionFileProperties)

ActiveWorkbook.SetPasswordEncryptionOptions _ 
 PasswordEncryptionProvider:="Microsoft RSA SChannel Cryptographic Provider", _ 
 PasswordEncryptionAlgorithm:="RC4", PasswordEncryptionKeyLength:=56,  PasswordEncryptionFileProperties:=True

Arguments

The following arguments are optional

PasswordEncryptionProvider - A case-sensitive string of the encryption provider

PasswordEncryptionAlgorithm - A case-sensitive string of the algorithmic short name (that is, "RC4")

PasswordEncryptionKeyLength - The encryption key length which is a multiple of 8 (40 or greater)

PasswordEncryptionFileProperties - True (default) to encrypt file properties

Unprotect

Removes protection from a sheet or workbook. This method has no effect if the sheet or workbook isn't protected.

If you forget the password, you cannot unprotect the sheet or workbook. It's a good idea to keep a list of your passwords and their corresponding document names in a safe place.

Unprotect (Password)

Password: A string that denotes the case-sensitive password to use to unprotect the sheet or workbook. If the sheet or workbook isn't protected with a password, this argument is ignored. If you omit this argument for a sheet that's protected with a password, you'll be prompted for the password. If you omit this argument for a workbook that's protected with a password, the method fails.

ActiveWorkbook.Unprotect

WritePassword

Returns or sets a String for the write password of a workbook.

ActiveWorkbook.WritePassword =

WriteReserved

True if the workbook is write-reserved.

Use the SaveAs method to set this property.

Dim booWriteReserved As Boolean
booWriteReserved = ActiveWorkbook.WriteReserved

WriteReservedBy

Returns the name of the user who currently has write permission for the workbook.

With ActiveWorkbook 
 If .WriteReserved = True Then 
 MsgBox "Please contact " & .WriteReservedBy & Chr(13) & _ 
 " if you need to insert data in this workbook." 
 End If 
End With