This Excel tutorial explains how to protect workbook with password with VBA and without VBA.
Different kinds of workbook protection
There are several kinds of protection in Excel.
- Add password to workbook. Users are asked to open the Excel file with password.
- Protect workbook with password. Protect the structure of all worksheets (do not allow insert, delete, rename, move, copy, hide or unhide worksheets)
- Hide worksheet with password. Hide the worksheet and add a password to unhide the worksheet.
- Protect worksheet (cells) with password. Restrict users to edit the cell
Excel protect workbook with password
Navigate to REVIEW > Protect Workbook
Check the Structure box or Windows box as appropriate.
Note that in Excel 2013, the Windows box is disabled. I am not sure if there is a way to enable it but even VBA doesn’t work.
|Check the box||Prevent users from|
|Structure||– Viewing worksheets you’ve hidden.
– Moving, deleting, hiding, or changing the names of worksheets.
– Inserting new worksheets or chart sheets.
– Moving or copying worksheets to another workbook.
– In PivotTable reports, displaying the source data for a cell in the data area, or displaying page field pages on separate worksheets.
– For scenarios, creating a scenario summary report.
– In the Analysis ToolPak, using the analysis tools that place results on a new worksheet.
|Windows||– Changing the size and position of the windows for the workbook when the workbook is opened.
– Moving, resizing, or closing the windows.
Right click on a worksheet, you will find many options are disabled.
Excel protect workbook with password (VBA)
Syntax of Workbook.Protect Method
expression .Protect(Password, Structure, Windows)
|Password||Optional||Variant||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. For more information, see Help protect your personal information with strong passwords. 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||Optional||Variant||True to protect the structure of the workbook (the relative position of the sheets). The default value is False.|
|Windows||Optional||Variant||True to protect the workbook windows. If this argument is omitted, the windows aren’t protected.|
Example of Workbook.Protect Method
To protect Workbook structure
ActiveWorkbook.Protect Structure:=True, Windows:=False
Syntax of Workbook.Unprotect
|Password||Optional||Variant||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.|
Example of Workbook.Unprotect