VBA Workbook Protection (Password Protect / Unprotect)

Excel allows you the ability to protect your Excel workbooks from changes. This tutorial will show you how to protect or unprotect workbook structure using VBA.

VBA Workbook Protection

VBA Workbook Protection allows you to lock the structure of the workbook. When a workbook is protected, users will be unable to add, delete, hide / unhide, or protect / unprotect worksheets. If you are building a model, you probably want to turn on workbook protection to prevent users (or you!) from accidentally deleting worksheets.

UnProtect Workbook VBA

To unprotect a workbook simply use the following line of code:

Workbooks("Book1").Unprotect

Note: this code will only work if the workbook was protected without a password. If it was protected with a password, you must also enter in the password to unprotect it:

UnProtect Workbook With Password

This line of code will unprotect a workbook that has been protected with a password:

Workbooks("Book1").Unprotect Password:="password"

or you can ommit Password:=

Workbooks("Book1").Unprotect "password"

UnProtect ThisWorkbook

This code will unprotect ThisWorkbook (ThisWorkbook is the workbook where the running code is stored. It will never change).

ThisWorkbook.Unprotect

or unprotect ThisWorkbook with a password:

ThisWorkbook.Unprotect "password"

UnProtect ActiveWorkbook

This code will unprotect the ActiveWorbook.

ActiveWorkbook.Unprotect

or unprotect the ActiveWorkbook with a password:

ActiveWorkbook.Unprotect "password"

UnProtect All Open Workbooks

This code will unprotect all open workbooks:

Sub UnprotectAllOpenWorkbooks() Dim wb As Workbook For Each wb In Workbooks wb.Unprotect Next wb End Sub

UnProtect Workbook – Without Knowing Password

If you need to unprotect a workbook without knowing the password, there are several add-ins to help. I would recommend Ribbon Commander.

UnProtect All Sheets in Workbook

After unprotecting a workbook, you might also want to unprotect all sheets in the workbook. Here’s a procedure that will unprotect all sheets:

Sub UnProtectWorkbookAndAllSheets() Dim ws As Worksheet ActiveWorkbook.Unprotect For Each ws In Worksheets ws.Unprotect Next End Sub

Protect Workbook

You can protect workbook structures in the same way as you unprotect.

Protect Workbook No Password

This line of code will protect a workbook (no password)

Workbooks("Book1").Protect

Note: I’ll often apply workbook protection without passwords, simply to prevent accidental changes to workbooks.

Protect Workbook With Password

This code will protect the workbook structure (with a password)

Workbooks("Book1").Protect "password"

Workbooks("Book1").Protect Password:="password"

Password Protect Excel File

Instead of workbook protection, you might want to password-protect an entire Excel file. To do that using VBA, Save As the workbook with a password:

Workbooks("Book1").SaveAs "password"

Protect / UnProtect Workbook Examples

Unhide All Worksheets in Protected Workbook

This procedure will unprotect a workbook, hide all worksheets, and re-protect the workbook

Sub UnprotectWB_Unhide_All_Sheets() Dim ws As Worksheet ActiveWorkbook.Unprotect For Each ws In Worksheets ws.Visible = xlSheetVisible Next ActiveWorkbook.Protect End Sub

Protect Workbook and All Sheets

This procedure will protect all worksheets in a workbook and then protect the workbook:

Sub ProtectWB_Protect_All_Sheets() Dim ws As Worksheet ActiveWorkbook.Unprotect For Each ws In Worksheets ws.Protect Next ActiveWorkbook.Protect End Sub

You can add password protection as well:

Sub ProtectWB_Protect_All_Sheets_Pswrd() Dim ws As Worksheet ActiveWorkbook.Unprotect "password" For Each ws In Worksheets ws.Protect "password" Next ActiveWorkbook.Protect "password" End Sub

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)