Different ways to protect a Workbook

Protecting a workbook may involve many different concernes which this page discusses and shows VBA implementation for.

workbook saveas password

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"
workbook Protect structure and windows

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.


workbook Protect Sharing

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.