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.
- 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 add password to workbook
Navigate to Save As dialog
Click on Tools > General Options
Type a password in Password to open > OK
Retype the password to confirm > OK
Now we have added a password to workbook.
Close and reopen the workbook.
Enter the password to open the workbook
Unprotect Workbook
After opening the workbook with password, open the Save As dialog
Click on Tools > General Options
Delete the password in Password to open > OK
Blank password means no password
Click on Save button
Click on Yes to overwrite the file with password.
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
