Excel Macro to Protect a Sheet: VBA Code and Setup

VBA & macros · Updated November 19, 2026

To protect an Excel worksheet with a macro, call the sheet’s VBA Protect method, for example Worksheets("Entry").Protect Password:="change-me". In Microsoft Excel for Microsoft 365 on Windows, put a procedure using that method in a VBA module, then run it; protection takes effect only after the macro runs.

What worksheet protection does

Worksheet protection controls what people can change in a sheet through Excel’s normal interface. It is useful for keeping formulas, labels, and layout intact while still allowing edits in designated input cells. It does not make a workbook confidential, encrypt its contents, or provide strong security against someone determined to inspect or alter the file.

Excel cells are marked as locked by default, but that setting has no practical effect until the sheet is protected. A typical workflow is to unlock the cells users should edit, leave formula and heading cells locked, and then protect the sheet. Protection can also restrict actions involving objects and scenarios, depending on the options you set.

Sheet protection is different from protecting a workbook’s structure. Sheet protection governs changes within a worksheet; workbook-structure protection helps prevent actions such as adding, deleting, moving, or renaming sheets. A macro that calls Worksheet.Protect protects a sheet, not the workbook structure.

Write a basic Protect macro

In Excel for Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste in the procedure below, and change the sheet name and password to suit the workbook. The code uses ThisWorkbook, meaning the workbook containing the macro; make sure that workbook contains a worksheet named Entry.

Sub ProtectEntrySheet()
    Dim ws As Worksheet

    ' Use the worksheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Entry")

    ' Protect cells, objects, and scenarios with a password.
    ws.Protect Password:="change-me", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Before running a macro, save a copy of the workbook: VBA actions cannot be reversed with Ctrl+Z. To run this procedure, return to Excel, press Alt+F8, select ProtectEntrySheet, and choose Run. The sheet is protected when the macro completes. The example’s password is a placeholder, not a recommended real password.

On a Mac, Excel also supports VBA macros, although menu names and keyboard shortcuts differ. You can open the Visual Basic Editor from Tools > Macro > Visual Basic Editor; on some Mac keyboards, a function-key shortcut may require the Fn key. Excel for the web can open a macro-enabled workbook but cannot create, edit, or run VBA macros, so use the desktop Excel app for this procedure.

Choose which cells and actions users can access

To let people enter information while protecting formulas, first select the intended input cells. In Excel for Microsoft 365 on Windows, open Format Cells with Ctrl+1, select the Protection tab, clear Locked, and choose OK. Then protect the worksheet. The unlocked cells remain editable, while locked cells are restricted by protection. On Mac, use Command+1 to open Format Cells; the dialog’s Protection tab serves the same purpose.

For example, a quote sheet might leave cells B3:B8 unlocked for customer details and quantities, while keeping the formula cells in D3:D8 locked. Protecting the sheet then helps prevent accidental overwriting of the calculations without preventing ordinary data entry in B3:B8. Test the finished sheet as a user would: try entering data in both an input cell and a formula cell.

The Protect method has optional arguments for permissions. For instance, AllowFiltering:=True can let users use an existing AutoFilter on a protected sheet. Filtering must already be enabled before protection, and this permission does not let users turn the AutoFilter on or off. AllowSorting:=True permits sorting under Excel’s protection rules; in some layouts, the cells in the sort range must be unlocked or unprotected for sorting to work.

Other options include allowing users to format cells, columns, or rows, insert or delete rows, and select locked or unlocked cells. Grant only the actions the sheet is meant to support. For example, allowing row insertion may be useful in a growing log, but it can also change the layout or affect formulas and references. The manual Review > Protect Sheet command exposes similar choices in a dialog, while VBA lets you apply chosen options consistently across repeated tasks.

Let a macro edit a protected sheet

A protected sheet can still be awkward if another macro needs to update its locked cells. One option is to unprotect the sheet, make the changes, and protect it again. Another is to apply protection with UserInterfaceOnly:=True. That setting keeps restrictions in place for users working in the interface while allowing VBA code to change protected cells.

Here is a procedure that sets this option for the Entry worksheet. Add it to a standard module and run it when the workbook opens, as shown in the next example, or call it from another procedure when appropriate.

Sub SetEntrySheetProtection()
    Dim ws As Worksheet

    ' Set the worksheet in the workbook containing this code.
    Set ws = ThisWorkbook.Worksheets("Entry")

    ' Allow VBA to edit protected cells, but restrict users.
    ws.Protect Password:="change-me", UserInterfaceOnly:=True
End Sub

There is an important detail: Excel does not preserve the UserInterfaceOnly setting after the workbook is closed. Set it again each time the workbook opens. In the Visual Basic Editor, open the ThisWorkbook code module and add this event procedure. Keep the regular protection procedure in a standard module, and use the same password in both places.

Private Sub Workbook_Open()
    Dim ws As Worksheet

    ' Reapply interface-only protection when this workbook opens.
    Set ws = ThisWorkbook.Worksheets("Entry")
    ws.Protect Password:="change-me", UserInterfaceOnly:=True
End Sub

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so it can retain VBA code. Close and reopen it with macros enabled to check that the event works. The workbook’s macro security settings may block the procedure, and users should enable macros only when they trust the file and its source.

Unprotect a sheet with VBA

To remove worksheet protection by macro, use the Unprotect method and provide the same password. This is useful when a controlled procedure must make changes that are not permitted under the current protection settings. The code below targets the same Entry worksheet in the workbook that contains the macro.

Sub UnprotectEntrySheet()
    Dim ws As Worksheet

    ' Use the worksheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Entry")

    ' Supply the password used when protecting the sheet.
    ws.Unprotect Password:="change-me"
End Sub

If the password is wrong, or the named worksheet does not exist, the procedure can return an error. A production macro can include error handling and a user-friendly message, but do not hide errors during setup: first confirm the sheet name and password. A macro that stores a password in its source code also makes that password visible to people who can inspect the VBA project, so do not treat that storage as secure.

Common problems and practical checks

  • Users cannot edit an input cell: Confirm that the cell’s Locked setting was cleared before protecting the sheet. Unlocking it after protection may require unprotecting the sheet first.
  • The macro says a subscript is out of range: Check the spelling of the worksheet tab name and confirm that the worksheet is in the workbook referenced by ThisWorkbook.
  • The macro does not run: Confirm the file is saved as .xlsm, that macros are enabled for this trusted file, and that the procedure is in a standard module if you are running it from Alt+F8.
  • A filter or sort command is unavailable: Check the protection permissions and whether the filter was applied before protection. Also check whether locked cells in the sort range prevent the requested operation.
  • VBA can no longer edit the sheet: If you rely on UserInterfaceOnly, reapply it when the workbook opens; Excel does not retain that option across sessions.

For a workbook used by other people, test more than the happy path. Try editing an unlocked input, changing a locked formula, filtering a table, and running any macro that updates the sheet. Protection settings that look correct in the VBA call may still make a particular workflow inconvenient unless the relevant permissions and cell states are tested together.

Protection is not encryption

Worksheet protection is best understood as a guard against routine edits and mistakes, not as a way to secure sensitive data. It does not stop a recipient from making a separate copy of accessible information, and it is not equivalent to encrypting a workbook with a password to open it. If confidentiality is important, use appropriate file-access controls and workbook encryption rather than relying on a protected sheet.

For ordinary collaboration, use a password that is not an obvious placeholder, limit permissions to the actions users need, and keep an unprotected backup in a controlled location. Remember that a password embedded in VBA can be read by someone with access to the code. If the goal is simply to prevent accidental formula edits, a clear input-cell design and sheet protection are often more practical than trying to make a workbook tamper-proof.

Frequently Asked Questions

Can a macro protect every worksheet in a workbook?
Yes. VBA can loop through the workbook’s worksheets and call Protect on each one. Decide whether every sheet should use the same password and permissions, and test the result, especially if some sheets need to remain editable.

Can users edit unlocked cells on a protected sheet?
Yes. Clear the Locked setting for the cells users should edit before applying protection. Protection restricts locked cells and allows the intended input cells to remain available.

Does sheet protection prevent someone from seeing formulas?
No. It primarily restricts changes. To hide formulas from the formula bar, you can set the cells’ Hidden property in Format Cells > Protection and then protect the sheet, but this is not encryption or strong information security.

Will the macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run or edit the macro.

Why does my macro need to protect the sheet again after reopening?
Ordinary sheet protection generally remains saved, but the special UserInterfaceOnly:=True behavior is not retained between sessions. Put that setting in the workbook’s Workbook_Open event so it is reapplied when the file opens.

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.