Excel Macro Protect Sheet Options: VBA, Limits, and Tips

VBA & macros · Updated November 19, 2026

In Excel for Microsoft 365 on Windows, a VBA macro protects a worksheet with the Worksheet.Protect method: choose a password and specify which actions remain allowed, such as filtering or formatting. For example, ws.Protect Password:="ChangeThisPassword", Contents:=True, AllowFiltering:=True protects cell contents while permitting use of an existing filter; it does not encrypt the workbook or make its data secure from determined access.

What worksheet protection does

Sheet protection is primarily a way to prevent accidental edits to selected parts of a worksheet. A typical design leaves input cells unlocked, locks formulas and labels, and then protects the sheet. Users can enter data in the intended fields without casually overwriting calculations. Protection also lets you permit a limited set of actions, but it is not a substitute for controlling who can open or copy a sensitive file.

Excel cells are marked as locked by default, but that setting has no practical effect until sheet protection is turned on. Once protection is active, locked cells are restricted according to the options chosen. Unlocked cells remain editable. The worksheet’s protection settings do not change the underlying values or formulas; they affect what users can do through the worksheet interface.

Sheet protection is different from protecting a workbook’s structure, which restricts actions such as adding, deleting, moving, or renaming sheets. It is also different from encrypting a workbook with a password to open. Those controls address different needs and are configured separately.

Protect method options that matter

The Protect method accepts a password, switches for protecting worksheet components, and optional permissions for particular actions. The most commonly used arguments are:

  • Password supplies the password needed to unprotect the sheet. It can be omitted, but then protection does not require a password to remove.
  • Contents protects locked cells. Set it explicitly to True when that is the intended behavior.
  • DrawingObjects controls protection of objects such as shapes and charts, while Scenarios applies to worksheet scenarios.
  • UserInterfaceOnly allows macros to make changes while the user interface remains protected. This setting has to be reapplied after the workbook is reopened.
  • AllowFormattingCells, AllowFormattingColumns, and AllowFormattingRows permit the corresponding formatting actions.
  • AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, and AllowDeletingRows permit those specific operations, subject to Excel’s other restrictions.
  • AllowSorting, AllowFiltering, and AllowUsingPivotTables allow sorting, using an existing filter, and using PivotTable reports, respectively.

These are permissions, not commands that create the feature being permitted. For instance, AllowFiltering:=True does not add filter arrows to a range; an AutoFilter must already be in place. Likewise, allowing PivotTable use does not create a PivotTable.

Prepare the sheet before protecting it

Decide which cells people should edit before running the protection macro. Select those cells, open Format Cells, choose the Protection tab, and clear the Locked check box. On Windows, press Ctrl+1 to open Format Cells; on Mac, use Command+1. Then turn on protection. In VBA, the corresponding cell property is Locked, so a macro can prepare known input ranges as well.

For example, if users enter information in B2:B20 while formulas elsewhere should remain locked, unlock B2:B20 and protect the sheet. The exact range depends on the workbook layout. Test with a normal user workflow: confirm that an input can be changed, a formula cannot be overwritten, and permitted actions still work. A sheet can be protected successfully yet feel unusable if its intended input cells were never unlocked.

A practical VBA protection macro

This example protects a worksheet named Entry, protects its contents and objects, and allows users to filter and sort where Excel permits those actions. Replace the example password and worksheet name with values appropriate to the workbook. Save a separate copy before running a macro: VBA changes cannot be undone with Ctrl+Z.

Option Explicit

Sub ProtectEntrySheet()
    Dim ws As Worksheet

    ' Use the worksheet name shown on its tab.
    Set ws = ThisWorkbook.Worksheets("Entry")

    ' Replace this example password before using the macro.
    ws.Protect Password:="ChangeThisPassword", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True

    ' Restrict selection to cells that users can edit.
    ws.EnableSelection = xlUnlockedCells
End Sub

The EnableSelection line is separate from the Protect arguments. xlUnlockedCells limits selection to unlocked cells; xlNoRestrictions allows selecting locked and unlocked cells, and xlNoSelection prevents selecting cells. Choose the setting that suits the worksheet, or omit the line if users need to select protected cells to copy or inspect them.

To run VBA in desktop Excel, save the file as an Excel Macro-Enabled Workbook (.xlsm), open the Visual Basic Editor, insert the procedure in a standard module, and run it with the correct sheet active or by selecting the macro in the Macros dialog. Excel for the web cannot run VBA macros, so use desktop Excel to run this code. Macro availability can also depend on the workbook’s security settings and whether the file is trusted.

Allowing sorting, filtering, and other actions

Protection options do not override every limitation of Excel. Filtering permission is for using an AutoFilter that is already applied; users generally cannot create or remove the filter while the sheet is protected. Sorting on a protected sheet can also be restricted when the sort range contains locked cells. If a permission appears not to work, check both the sheet setup and the nature of the range rather than assuming the argument was ignored.

Allow only the actions users genuinely need. For an input form, that might mean permitting selection of unlocked cells but not formatting, inserting rows, or deleting columns. For a reporting sheet, filtering and sorting may be useful, while editing locked formula cells should remain blocked. Granting formatting permissions can make a sheet easier to customize, but it can also make a carefully designed layout less consistent.

Excel’s Review tab includes a Protect Sheet command for manual protection. A macro is more useful when protection must be applied consistently across repeated workbooks or as part of a workflow. It can also set permissions that would otherwise require a user to repeat setup steps. A macro is not required simply because a sheet needs protection; the built-in command may be easier to maintain for a one-off workbook.

Using UserInterfaceOnly safely

UserInterfaceOnly:=True is useful when people should be blocked from editing protected cells but a macro needs to update them. It separates the user-interface restriction from changes made by VBA. For example, a macro can write a timestamp or refresh a formula result on a protected sheet without asking the user to unprotect it first.

There is an important catch: Excel does not retain the UserInterfaceOnly setting when the workbook is closed and reopened. If a macro depends on it, apply protection again each time the workbook opens. Put this event procedure in the ThisWorkbook code module, not in a standard module. Save a backup copy before testing it.

Option Explicit

Private Sub Workbook_Open()
    Dim ws As Worksheet

    ' Reapply protection so VBA can edit the sheet after reopening.
    Set ws = ThisWorkbook.Worksheets("Entry")
    ws.Protect Password:="ChangeThisPassword", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, AllowFiltering:=True, AllowSorting:=True
End Sub

The workbook must be saved in a macro-enabled format, and macros must be allowed to run for the open event to execute. If macros are disabled, the event will not reapply the setting. Keep the password and macro behavior consistent: a mismatch between the password used in the open event and the one already protecting the sheet can cause an error.

Unprotecting and changing options

To remove protection in VBA, use the worksheet’s Unprotect method. This example expects the same password used to protect the sheet. Save a copy before running it, since macro actions cannot be reversed with Ctrl+Z.

Option Explicit

Sub UnprotectEntrySheet()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Entry")
    ws.Unprotect Password:="ChangeThisPassword"
End Sub

To change permissions, unprotect the sheet and protect it again with the new argument values. If the password is incorrect, Excel will not unprotect the sheet. A macro should handle expected errors thoughtfully rather than silently continuing as though protection changed. Also consider whether users need to know the password: if the code includes it as plain text, someone who can inspect the VBA project may be able to read it. Locking a VBA project can deter casual inspection, but it should not be treated as strong security for confidential data.

Common problems and security limits

  • Users can still edit certain cells: those cells may be unlocked, or the macro may be protecting a different worksheet than expected. Check the sheet name and the cells’ Locked setting.
  • Filtering does not work: confirm an AutoFilter already exists before protection, and check that the user is using the existing filter rather than trying to create one.
  • A macro cannot edit a protected cell: check that protection was applied with UserInterfaceOnly:=True during the current Excel session. Reapply it on workbook open if needed.
  • The macro reports a subscript error: the worksheet name in the code may not match the tab name. ThisWorkbook refers to the workbook containing the macro, which is usually appropriate when the code is stored in that workbook.
  • Protection seems to disappear after reopening: inspect the code and open-event behavior. UserInterfaceOnly resets on reopening, while ordinary sheet protection remains in place.

Worksheet protection is designed mainly to guard against accidental changes, not to provide confidentiality. It does not encrypt the file, prevent all copying, or replace permissions on the file or storage location. For sensitive information, restrict access to the workbook and use Excel’s file-encryption options where appropriate. Do not put a real secret in VBA under the assumption that worksheet protection makes it inaccessible.

Frequently Asked Questions

What is the VBA command to protect an Excel sheet?
Use the worksheet’s Protect method, such as Worksheets("Entry").Protect Password:="Example", Contents:=True. Set any additional permissions explicitly so the macro’s intended behavior is clear.

Can a macro protect a sheet but let users edit input cells?
Yes. Unlock the input cells before protection, then protect the sheet. Locked cells are restricted and unlocked cells remain editable under sheet protection.

Does AllowFiltering add filter buttons?
No. AllowFiltering:=True allows use of an AutoFilter that is already applied; it does not create the filter or its arrows.

Why does UserInterfaceOnly stop working after I reopen the file?
Excel does not save that setting between sessions. Reapply protection with UserInterfaceOnly:=True in the workbook’s Workbook_Open event.

Can Excel for the web run a sheet-protection macro?
No. Excel for the web does not run VBA. Open the macro-enabled workbook in desktop Excel to run the macro; web-based protection controls are separate from running VBA.

Is a protected worksheet password secure encryption?
No. Sheet protection is intended to limit editing, especially accidental changes. It does not encrypt workbook contents or provide strong confidentiality.

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.