Return to VBA Code Examples

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.

vba protect workbook

UnProtect Workbook VBA

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

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:

or you can ommit Password:=

UnProtect ThisWorkbook

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

or unprotect ThisWorkbook with a password:

UnProtect ActiveWorkbook

This code will unprotect the ActiveWorbook.

or unprotect the ActiveWorkbook with a password:

UnProtect All Open Workbooks

This code will unprotect all open workbooks:

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:

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)

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)

or:

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:

Protect / UnProtect Workbook Examples

Unhide All Worksheets in Protected Workbook

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

Protect Workbook and All Sheets

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

You can add password protection as well: