Excel VBA Protect Cells: Lock and Unlock Ranges

VBA & macros · Updated November 22, 2026

To protect specific cells in Excel with VBA, you must first unlock the cells you want editable, then enable worksheet protection with the Protect method. The core pattern is: set Range.Locked = False for editable cells, then call Worksheet.Protect to enforce the lock on all other cells. This article explains the full process, including how to protect only certain ranges, allow specific actions, and handle common pitfalls.

Why Cell Protection Requires Two Steps

Excel’s protection model is counterintuitive at first: every cell has a Locked property, but that property has no effect until the worksheet is protected. By default, all cells have Locked = True, but because the sheet is unprotected, users can edit anything. When you protect the sheet, all cells with Locked = True become read-only, while cells with Locked = False remain editable. This two-step design lets you predefine editable regions before enabling protection.

In VBA, the process is:

  1. Identify the range you want users to edit (e.g., input cells).
  2. Set Range.Locked = False for that range.
  3. Call Worksheet.Protect to activate protection.

If you forget step 2, the entire sheet becomes locked after protection. If you forget step 3, the Locked property does nothing.

Basic VBA Code to Protect Cells

Here is a simple macro that unlocks cells A1:B10 and then protects the sheet, leaving all other cells locked:

Sub ProtectSpecificCells()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Unlock the editable range
    ws.Range("A1:B10").Locked = False
    
    ' Protect the sheet (no password)
    ws.Protect Password:="", UserInterfaceOnly:=True
End Sub

Note the UserInterfaceOnly:=True parameter. It allows VBA macros to modify the sheet even while it is protected, but the user cannot edit via the interface. This is useful if you have other macros that need to write to locked cells. Without this parameter, your own VBA code would also be blocked from changing cells unless you unprotect first.

If you want a password, replace the empty string with your password, e.g., Password:="MyPass". Remember: if you forget the password, you cannot unprotect the sheet without third-party tools.

Protecting Only a Specific Range (Allow Edit Range)

Excel has a built-in feature called “Allow Users to Edit Ranges” that works with VBA. You can add editable ranges programmatically using the Protection.AllowEditRanges collection. This is more flexible because you can assign different passwords to different ranges.

Example: allow editing of B2:C5 without a password, and D2:E5 with password “secret”:

Sub AddEditRanges()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Clear existing ranges (optional)
    ws.Protection.AllowEditRanges.Delete
    
    ' Add a range with no password
    ws.Protection.AllowEditRanges.Add Title:="Input", _
        Range:=ws.Range("B2:C5")
    
    ' Add a range with password
    ws.Protection.AllowEditRanges.Add Title:="Secret", _
        Range:=ws.Range("D2:E5"), Password:="secret"
    
    ' Protect the sheet
    ws.Protect
End Sub

When the sheet is protected, users can edit B2:C5 without prompting, but editing D2:E5 will ask for the password “secret”. This is ideal for collaborative workbooks where different users have different permissions.

Allowing Specific Actions While Protected

Protecting a sheet locks more than just cell editing. By default, users cannot select locked cells, format rows/columns, insert rows, etc. You can control these via the Protect method’s parameters. For example, to allow users to select locked cells but not edit them, use AllowSelectingLocked:=True. To allow sorting or autofilter, set AllowSorting:=True and AllowFiltering:=True.

Full syntax example:

ws.Protect Password:="pass", _
    AllowSelectingLockedCells:=True, _
    AllowSelectingUnlockedCells:=True, _
    AllowSorting:=True, _
    AllowFiltering:=True, _
    AllowFormattingCells:=False

Common parameters include:

  • AllowSelectingLockedCells – allow clicking on locked cells (default False).
  • AllowSelectingUnlockedCells – allow selecting editable cells (default True).
  • AllowSorting – allow sorting ranges that are not locked.
  • AllowFiltering – allow using AutoFilter.
  • AllowFormattingCells – allow changing cell format (e.g., bold).
  • AllowInsertingRows – allow inserting rows.

Set each to True or False as needed. If you omit them, they default to False, which can be frustrating if you want users to sort data.

Unprotecting and Re-protecting in VBA

To unprotect a sheet in VBA, use ws.Unprotect Password:="yourpassword". If no password was set, just ws.Unprotect. You often need to unprotect before making changes to locked cells, then re-protect after. Here is a pattern:

Sub ModifyLockedCell()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Unprotect
    ws.Unprotect Password:="pass"
    
    ' Make changes
    ws.Range("A1").Value = "New Value"
    
    ' Re-protect
    ws.Protect Password:="pass"
End Sub

If you used UserInterfaceOnly:=True when protecting, you do not need to unprotect for VBA to write to cells. However, that setting is not persisted when the workbook is saved and reopened; you must reapply it every time the workbook opens (e.g., in a Workbook_Open event).

Common Problems and Solutions

Here are frequent issues users encounter:

  • Entire sheet is locked after protection: You forgot to set Locked = False on the editable range before protecting. Always set the range’s Locked property before calling Protect.
  • VBA cannot modify locked cells even with UserInterfaceOnly: That parameter only works during the current session. After closing and reopening the workbook, protection blocks VBA too. Solution: reapply UserInterfaceOnly:=True in the Workbook_Open event, or unprotect before writing.
  • Users cannot select locked cells, making navigation hard: Set AllowSelectingLockedCells:=True in the Protect call.
  • Sorting or filtering fails on protected sheet: You must enable AllowSorting and AllowFiltering in the Protect method. Note that sorting requires all cells in the sort range to be unlocked.
  • Password forgotten: There is no built-in way to recover a lost password. Always keep a backup copy of the workbook without protection.

Comparing VBA Protection with Manual Protection

You can achieve the same result manually via the ribbon: select cells, open Format Cells (Ctrl+1), go to the Protection tab, uncheck “Locked”, then go to Review > Protect Sheet. VBA is useful when you need to automate this for many sheets or when you want to change protection dynamically based on user input. Manual protection is fine for one-time setups, but VBA allows you to:

  • Protect multiple sheets in a loop.
  • Set different passwords for different ranges.
  • Reapply protection with specific settings on workbook open.
  • Integrate protection with userforms or conditional logic.

Performance-wise, VBA is instantaneous; there is no noticeable difference. The main advantage of VBA is repeatability and flexibility.

Frequently Asked Questions

How do I protect only certain cells in Excel with VBA?
Set the Locked property of the cells you want editable to False, then protect the sheet. For example: Range("A1:A10").Locked = False followed by ActiveSheet.Protect. All other cells remain locked.

Why does my VBA code not protect cells?
Most likely you forgot to call Protect after setting Locked. The Locked property alone does nothing until the sheet is protected. Also check that the worksheet is not already protected with a password you don’t know.

Can I protect cells but allow sorting in Excel VBA?
Yes, use ws.Protect AllowSorting:=True. However, sorting only works if the sort range consists entirely of unlocked cells. If any cell in the sort range is locked, sorting will fail.

How do I unprotect a sheet in VBA without a password?
If you protected without a password, simply call ws.Unprotect. If you used a password, you must provide it: ws.Unprotect Password:="mypassword". There is no way to bypass a password in VBA.

Does protecting cells prevent VBA macros from changing them?
By default, yes. VBA cannot modify locked cells on a protected sheet unless you use UserInterfaceOnly:=True when protecting. That setting allows macros to work but must be reapplied each time the workbook opens.

Can I protect cells in Excel for the web with VBA?
No. VBA is not supported in Excel for the web. You can manually protect sheets via the Review tab, but you cannot run VBA macros in the browser version. Use the desktop version of Excel for VBA automation.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.