Different ways to protect a Workbook
Protecting a workbook may involve many different concernes which this page discusses and shows VBA implementation for.
- Prevent a user from opening and/or making changes to a workbook
- Protect the workbooks structure (add / remove / reorder worksheets) and windows
- Prevent change to the workbooks Sharing settings
- Protect the individual worksheets content and other properies such as filters
Prevent to open and or change a workbook.
To prevent unauthorized users from opening or changing of a workbook you can give it a password. Use VBASaveAs method of the workbook class.
Dim wb As Workbook: Set wb =
Dim strFilename As String: strFilename =
wb.SaveAs Filename:=strFilename, FileFormat:=xlOpenXMLWorkbook, Password:="123"
Protect workbook structure and windows
When you protect a workbook, you prevent users from adding or deleting worksheets and preventing the user from moving worksheets within the workbook. The Windows option prevents the user from creating additional windows for the workbook.
Dim wb As Workbook: Set wb =
wb.Protect Password:="123", Structure:=True, Windows:=False
To undo protection use wb.UnProtect - with password if originally specified.
ProtectSharing Workbook
Saves the workbook and protects it for sharing.
Dim wb As Workbook
wb.ProtectSharing
- Filename: A string indicating the name of the saved file.You can include a full path; if you don't Microsoft Excel saves the file in the current folder.
- Password: the protection password to be given to the file. It should be no longer than 15 characters.
- WriteResPassword: the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened the file is opened read-only.
- ReadOnlyRecommended: True to display a message when the file is opened recommending that the file be opened read-only.
- CreateBackup: True to create a backup file.
- SharingPassword: the password to be used to protect the file for sharing.
- FileFormat: A string indicating the file format.