Excel protect workbook with password

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.

  1. Add password to workbook. Users are asked to open the Excel file with password.
  2. Protect workbook with password. Protect the structure of all worksheets (do not allow insert, delete, rename, move, copy, hide or unhide worksheets)
  3. Hide worksheet with password. Hide the worksheet and add a password to unhide the worksheet.
  4. Protect worksheet (cells) with password. Restrict users to edit the cell

Excel protect workbook with password

Navigate to REVIEW > Protect Workbook

excel protect workbook with password 01

 

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.

excel protect workbook with password 02

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 03

 

Excel protect workbook with password (VBA)

Syntax of Workbook.Protect Method

expression .Protect(Password, Structure, Windows)
Name Required/Optional Data Type Description
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

expression .Unprotect(Password)
Name Required/Optional Data Type Description
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

ActiveWorkbook.Unprotect

 

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff193800.aspx

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *