Excel add password to workbook

This Excel tutorial explains how to add password to workbook with VBA and without VBA.

You may also want to read

Add password to all Excel workbook in folder

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 add password to workbook

Navigate to Save As dialog

Excel add password to workbook 01

 

Click on Tools > General Options

Excel add password to workbook 02

 

Type a password in Password to open > OK

Excel add password to workbook 03

 

Retype the password to confirm > OK

Excel add password to workbook 04

Now we have added a password to workbook.

Close and reopen the workbook.

 

Enter the password to open the workbook

Excel add password to workbook 05

 

Unprotect Workbook

After opening the workbook with password, open the Save As dialog

Excel add password to workbook 06

 

Click on Tools > General Options

Excel add password to workbook 07

 

Delete the password in Password to open > OK

Blank password means no password

Excel add password to workbook 08

 

Click on Save button

Excel protect workbook 09

 

Click on Yes to overwrite the file with password.

Excel protect workbook 10

Now the workbook is no longer protected.

 

Excel add password to workbook (VBA)

In VBA, Workbook.SaveAs Method is same as the above method.

Syntax of Workbook.SaveAs

expression .SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

Example

The below procedure uses SaveAs Method to add password (yourpassword) and then overwrite the existing xlsm workbook.

Sub add_password()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="yourpassword"
    Application.DisplayAlerts = True
End Sub

Forget Workbook password

Unfortunately there is no official way to recover password. In fact, any password can be cracked with third party password generator (trying all possible combination) or simply writing an Excel Macro. However, as an Excel MVP, I would not encourage you to do that but you can google the information.

 

Outbound References

https://support.office.com/en-us/article/Password-protect-a-workbook-7e365a4d-3e89-4616-84ca-1931257c1517

 

 

 

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 *