Excel VBA: Protect a Sheet and Manage Permissions Safely

Yes. In Excel VBA, protect a worksheet with its Protect method, for example Worksheets("Entry").Protect Password:="demo-pass". Add options such as filtering or macro-only editing when needed, and remember that sheet protection helps prevent accidental changes; it is not a reliable way to keep confidential data secret.

What worksheet protection does

Protecting a sheet restricts actions users can take on that worksheet, according to the options you select. Commonly, it prevents editing locked cells while allowing users to select cells, work with permitted objects, or use particular features such as filters. You can still use VBA to protect or unprotect a sheet, provided the macro has the necessary password when one is set.

Protection does not automatically lock every cell in practice. Excel cells are marked as locked by default, but that setting only takes effect after the worksheet is protected. A typical form therefore leaves formula cells locked and unlocks the cells intended for data entry before protection is applied.

Worksheet protection is different from workbook-structure protection. Protecting a sheet controls actions on that sheet; protecting workbook structure restricts actions such as adding, deleting, moving, or renaming sheets. Neither is the same as encrypting a file so that a password is required to open it.

Protect a worksheet with a basic VBA macro

In Excel for Microsoft 365 on Windows, save a copy of the workbook before running a macro: VBA changes cannot be reversed with Ctrl+Z. A simple example is below. Change the sheet name and sample password to match your workbook. The password shown is only an example, not a secure way to store a real password.

Sub ProtectEntrySheet()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Entry")
    ws.Protect Password:="demo-pass", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

The reference uses ThisWorkbook, meaning the workbook that contains the macro, and expects a worksheet named Entry. If the sheet has another name, replace that text with its actual tab name. Contents:=True protects worksheet cell contents; the other arguments request protection for drawing objects and scenarios. These are common explicit settings, though the method also has other options.

To enter the code on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module. Run it with the cursor inside the procedure by pressing F5, or run it from Excel using Developer > Macros. If the Developer tab is hidden, enable it in Excel Options > Customize Ribbon. On a Mac, open the Visual Basic Editor from the Developer tab; keyboard shortcuts can depend on the keyboard and function-key settings. Excel for the web does not run VBA macros, so use the desktop app.

Save a macro-enabled workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA. Saving as .xlsx removes VBA content. When the workbook is reopened, Excel may require the user to enable macros, depending on its security settings and the file’s source.

Allow users to edit inputs but protect formulas

A useful pattern is to unlock only the cells users should change, then protect the sheet. Select the input cells and open Format Cells: use Ctrl+1 on Windows or Command+1 on Mac, choose the Protection tab, and clear Locked. Then apply protection. The locked status of the remaining cells becomes meaningful once protection is active.

For a form with editable cells B3:B10 and formulas elsewhere, the intended result is that users can enter values in B3:B10 while protected formula cells cannot be edited through the normal worksheet interface. The VBA macro does not need to unlock cells; cell locking is a worksheet format setting you prepare before protection.

When you protect a sheet using Review > Protect Sheet, Excel presents a list of actions that may remain available. VBA exposes many of those choices as named arguments. For example, you can allow filtering on a sheet that already has AutoFilter arrows:

Sub ProtectEntrySheetWithFiltering()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Entry")
    ws.Protect Password:="demo-pass", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        AllowFiltering:=True
End Sub

Save a copy before running this macro; Ctrl+Z cannot undo its protection change. The arrows should be in place before protection is applied. Allowing filtering lets users change criteria in an existing AutoFilter, but it does not let them turn AutoFilter on or off while the sheet is protected. Other available arguments include permissions for formatting cells, rows, or columns, inserting rows or columns, and sorting. Grant only the actions users actually need.

Unprotect a sheet with VBA

Use the worksheet’s Unprotect method to remove protection. If the sheet was protected with a password, the macro must supply the matching password. Save a copy of the workbook before running this macro as well, because VBA changes cannot be undone with Ctrl+Z.

Sub UnprotectEntrySheet()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Entry")
    ws.Unprotect Password:="demo-pass"
End Sub

If there was no password, the password argument can be left out: ws.Unprotect. A misspelled sheet name causes a run-time error, and a wrong password prevents the password-protected sheet from being unprotected. A macro that unprotects, updates, and reprotects a sheet can be useful for a controlled workflow, but it should handle errors so the sheet is not accidentally left unprotected if an update fails.

Let VBA edit a protected sheet

Sometimes users should be blocked from editing locked cells while a macro still needs to update them. In that case, use UserInterfaceOnly:=True when protecting the sheet. This setting applies protection to the user interface while allowing VBA procedures to make changes that would otherwise be blocked.

Excel does not retain the UserInterfaceOnly setting when the workbook is closed. Reapply it whenever the workbook opens. Put this event procedure in the ThisWorkbook code module, not in a standard module. Save a copy before adding or running VBA, since macro actions cannot be undone with Ctrl+Z.

Private Sub Workbook_Open()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Entry")
    ws.Protect Password:="demo-pass", _
        DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True
End Sub

To place it, open the Visual Basic Editor, find the workbook in the Project Explorer, and open its ThisWorkbook object. The workbook must be saved as .xlsm, and macros must be enabled for the event to run. Because this example contains a password directly in the VBA project, it should not be treated as a secure password-storage system.

Passwords, safety, and realistic limits

A worksheet password is useful for discouraging casual edits and preventing accidental changes, but worksheet protection is not strong file security. It should not be used to protect sensitive personal, business, or financial information from someone determined to access it. Also, a password typed directly into VBA can be viewed by people who can inspect the project, even if the project is locked against casual viewing.

If the goal is to prevent unauthorized people from opening a workbook, use file-level encryption rather than relying on a protected sheet. In Excel for Microsoft 365 on Windows, the relevant command is File > Info > Protect Workbook > Encrypt with Password. Keep a safe record of that password: losing an encryption password can prevent access to the file. Treat worksheet permissions as a workflow aid, not as a substitute for access control or secure storage.

Use sheet protection to reduce accidental edits to formulas or layouts.
Unlock intended input cells before protecting the sheet.
Keep passwords out of shared VBA code when they need to remain confidential.
Use file encryption when the requirement is restricting who can open the workbook.

Common problems and how to fix them

The macro says the subscript is out of range: Check that the worksheet name is spelled exactly as it appears on the tab and that the sheet is in the workbook referenced by ThisWorkbook.
Users still cannot edit an input cell: Check that the cell’s Locked setting was cleared before protection, and confirm that the sheet was protected after the change.
Filtering is unavailable: Add AutoFilter arrows before protecting the sheet, then use AllowFiltering:=True. This permission does not add arrows or allow users to create a new filter on a protected sheet.
A macro cannot edit locked cells: If users must remain restricted while code makes changes, protect the sheet with UserInterfaceOnly:=True. Reapply it in Workbook_Open because it is not retained between sessions.
The macro does not run: Check that the file is macro-enabled, that macros are permitted by Excel’s security settings, and that the code is in a suitable module. The Excel web app cannot execute VBA.

Sorting has an important limitation: Excel may prevent sorting on a protected sheet when the sort range contains locked cells. If sorting is essential, test the exact range and permissions with a copy of the workbook, and consider whether those cells can safely be unlocked. Protection options do not override every restriction that comes from the layout or the cells’ locked status.

Frequently Asked Questions

What is the VBA command to protect a sheet?
Use Worksheet.Protect, such as Worksheets("Entry").Protect Password:="demo-pass". Add named arguments to control which actions users can perform.

How do I protect every worksheet in a workbook?
Loop through the workbook’s worksheets and call Protect on each one. Before doing so, decide whether every sheet should use the same password and permissions; a single blanket rule may not suit sheets with different input or filtering needs.

Can a macro edit a protected sheet?
Yes. One option is to unprotect the sheet, make the change, and protect it again. Another is to apply protection with UserInterfaceOnly:=True, then reapply that setting each time the workbook opens.

Does protecting a sheet encrypt its contents?
No. Sheet protection restricts ordinary editing actions; it does not encrypt the workbook or reliably hide data. Use file-level encryption if people should not be able to open the file.

Can I run an Excel VBA protection macro in a browser?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the Excel desktop app to use its VBA procedures.

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.