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"

or:

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