Protect & Unprotect Sheets with Password (Code Examples)

Home » pub » Tutorials » Protect & Unprotect Sheets with Password (Code Examples)

Protect your Excel worksheets and control what users can edit with this simple password protection tutorial. Learn how to protect a sheet, set a password, lock or unlock specific cells, protect formulas, allow selected actions, and unprotect sheets when changes are needed. Ideal for Excel users, finance teams, analysts, and professionals who want to secure worksheet content and prevent accidental changes.

Protecting a worksheet prevents accidental edits to formulas, layouts, and locked cells. VBA lets you protect and unprotect sheets programmatically — with or without a password — making it easy to lock multiple sheets at once, toggle protection from a button, or protect sheets automatically when a workbook is closed. This is far faster than clicking through the Review ribbon for each sheet individually.

This guide covers the key VBA Protect method arguments, six practical examples, and common issues. Examples include single-sheet protection, protecting all sheets in a workbook, protecting with a password, unprotecting safely, allowing specific user actions, and an auto-protect-on-close pattern.

Protect vs locked cells: Sheet protection is separate from cell locking. By default, all cells are locked. However, locking only takes effect when the sheet is protected. To allow users to edit specific cells, unlock them first (Format Cells > Protection > uncheck Locked), then protect the sheet.

Protect and Unprotect Syntax

The Protect and Unprotect methods belong to the Worksheet object. Both accept a Password argument. Protect also accepts optional flags that allow specific user actions even when the sheet is protected.

Protect syntax: Worksheet.Protect [Password], [DrawingObjects], [Contents], [Scenarios], [UserInterfaceOnly], [AllowFormattingCells], [AllowInsertingRows], [AllowDeletingRows], [AllowSorting], [AllowFiltering] Unprotect syntax: Worksheet.Unprotect [Password] Most common usage: protect Contents only with a password. ActiveSheet.Protect Password:="pass123", Contents:=True

Examples 1–4: Protect and Unprotect in Practice

Protect the active sheet with a password

The simplest protection pattern locks the active sheet so no cells can be edited. Adding a password means users must provide it to unprotect the sheet via the ribbon or via VBA.

Sub ProtectSheet() ActiveSheet.Protect Password:="MySecret123", _ Contents:=True, _ DrawingObjects:=True, _ Scenarios:=True MsgBox "Sheet protected." End Sub Sub UnprotectSheet() ActiveSheet.Unprotect Password:="MySecret123" MsgBox "Sheet unprotected." End Sub

Protect all sheets in the workbook at once

Looping through the Worksheets collection protects every sheet in one pass. This is much faster than clicking the Review ribbon on each sheet individually. A single password applies to all sheets equally.

Sub ProtectAllSheets() Dim ws As Worksheet Dim pwd As String pwd = "MySecret123" For Each ws In ThisWorkbook.Worksheets ws.Protect Password:=pwd, Contents:=True Next ws MsgBox "All sheets protected." End Sub Sub UnprotectAllSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Unprotect Password:="MySecret123" Next ws MsgBox "All sheets unprotected." End Sub

Allow sorting and filtering while protected

By default, protection blocks all user actions including sorting and filtering. The AllowSorting and AllowFiltering flags override this selectively. Users can therefore sort and filter the data while the formula rows and column headers remain locked.

Sub ProtectAllowFilter() ActiveSheet.Protect _ Password:="MySecret123", _ Contents:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowFormattingCells:=False, _ AllowInsertingRows:=False, _ AllowDeletingRows:=False End Sub ' Result: users can sort and filter but cannot ' edit, format cells, insert rows, or delete rows.

Safe unprotect — check protection status first

Calling Unprotect on an already-unprotected sheet causes an error if a password is provided. The ProtectContents property checks whether protection is active first. This pattern prevents unnecessary errors when macros run conditionally.

Sub SafeUnprotect() Dim ws As Worksheet Dim pwd As String Set ws = ActiveSheet pwd = "MySecret123" If ws.ProtectContents Then On Error Resume Next ws.Unprotect Password:=pwd If ws.ProtectContents Then MsgBox "Incorrect password." Else MsgBox "Sheet unprotected." End If On Error GoTo 0 Else MsgBox "Sheet was not protected." End If End Sub

Examples 5–6: UserInterfaceOnly and Auto-Protect on Close

UserInterfaceOnly — protect from users but not from macros

The UserInterfaceOnly flag is a powerful option. It protects the sheet from manual user edits while allowing VBA macros to write to locked cells without needing to unprotect first. This is ideal for dashboards that update via macros but must remain locked against accidental user changes.

Sub ProtectUIOnly() ActiveSheet.Protect Password:="MySecret123", _ UserInterfaceOnly:=True End Sub ' After this: ' - Users cannot edit locked cells via keyboard or mouse. ' - VBA CAN write to any cell without calling Unprotect first. ' - UserInterfaceOnly resets to False when the workbook is closed. ' - Re-apply it in Workbook_Open() to persist on every open.

Auto-protect on close — Workbook_BeforeClose event

The Workbook_BeforeClose event runs just before the workbook closes. Placing the protect code there ensures the sheet is always locked when the file is saved and distributed. Users who open the file always see a protected sheet, regardless of the state when it was last used.

' Place this in the ThisWorkbook module (not a standard module). Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet For Each ws In Me.Worksheets ws.Protect Password:="MySecret123", Contents:=True Next ws Me.Save End Sub ' Me.Save saves the workbook in its protected state. ' Every sheet is locked before the file is written to disk.

Common Issues and How to Fix Them

Run-time error 1004 when writing to a protected sheet via VBA

This error occurs when your macro tries to write to a locked cell on a protected sheet without using UserInterfaceOnly. The quickest fix is to unprotect the sheet at the start of the macro and re-protect it at the end. Alternatively, set UserInterfaceOnly:=True when you protect the sheet. This allows VBA to write freely without needing to toggle protection on and off.

Password is forgotten — how to remove protection

Excel sheet protection uses a weak algorithm that third-party tools can remove quickly. If the password is lost, search for "Excel sheet password remover" — several free online utilities can unlock the sheet. For workbook-level passwords (File → Info → Protect Workbook), the same approach applies. Note that this removes the protection entirely, so set a new password afterwards if the sheet needs to stay protected.

Frequently Asked Questions

How do I protect a sheet with VBA?+

Call the Protect method on a Worksheet object. For example: ActiveSheet.Protect Password:="pass123", Contents:=True. The Contents:=True argument locks all cells from editing. If you omit the password, users can unprotect the sheet via the Review ribbon without needing to enter anything. Always store the password securely — lost passwords require third-party tools to remove.
Can VBA write to a protected sheet without unprotecting it?+

Yes, use the UserInterfaceOnly:=True parameter when calling Protect. This allows all VBA code to write to any cell without calling Unprotect first. Manual user edits via the keyboard or mouse are still blocked. Note that UserInterfaceOnly resets to False when the workbook is closed and reopened. Re-apply it in the Workbook_Open event to persist the setting.
How do I allow users to sort or filter a protected sheet?+

Use the AllowSorting and AllowFiltering parameters when calling Protect. For example: ActiveSheet.Protect Password:="pass", AllowSorting:=True, AllowFiltering:=True. Users can then sort and filter the data normally while formula rows and header cells remain locked against editing.
How do I check if a sheet is already protected before unprotecting?+

Use the ProtectContents property. If ws.ProtectContents is True, the sheet is protected and Unprotect is needed. If it is False, the sheet is already unlocked and calling Unprotect is unnecessary. This check prevents errors in macros that run regardless of the sheet's current protection state.