In this Article
- VBA Workbook Protection
- UnProtect Workbook VBA
- Protect Workbook
- Protect / UnProtect Workbook Examples
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:
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:=
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:
This code will unprotect the ActiveWorbook.
or unprotect the ActiveWorkbook with a 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
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)
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
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