Excel Macro to Protect a Sheet With a Password

To protect the active worksheet with a password in Excel for Microsoft 365 on Windows, run a VBA macro that calls the sheet’s Protect method. Change the example password before using it, and save a copy of your workbook first because macros cannot be undone with Ctrl+Z.

Sub ProtectActiveSheet()
    Dim ws As Worksheet
    Dim sheetPassword As String

    ' Replace this example with a password you choose.
    sheetPassword = "ChangeThisPassword"
    Set ws = ActiveSheet

    ws.Protect Password:=sheetPassword, _
        DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

This protects the active sheet’s cells, drawing objects, and scenarios. It does not encrypt the workbook or make its contents confidential. The distinction matters: worksheet protection is mainly a way to prevent accidental or routine changes, not a dependable security barrier against someone determined to bypass it.

How to add and run the protection macro

In desktop Excel for Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the code into the new module, and return to Excel. Select the worksheet you intend to protect, then press Alt+F8, select ProtectActiveSheet, and choose Run. The macro acts on the sheet that is active when it runs, so confirm the selected tab first.

Make a backup copy of the workbook before running VBA.
Replace ChangeThisPassword with your chosen password. Do not include angle brackets or explanatory text.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) so the VBA project is retained.
Run the macro and check that you can no longer edit a locked cell.

Excel may show a security notice when you open a macro-enabled file. Run macros only in workbooks you trust; do not enable macros in an unexpected file just to use sheet protection. In Excel for Mac, VBA is available in the desktop app, though the keyboard shortcut for the editor can depend on the keyboard and function-key settings. Use the Developer tab’s Visual Basic command if the shortcut does not open it. Excel for the web cannot create, edit, or run VBA macros; open the file in desktop Excel instead.

What worksheet protection does

Excel worksheets have protection settings that determine which actions are allowed after protection is switched on. By default, cells are marked as locked, but that setting has no effect until you protect the sheet. Once protected, users generally cannot change locked cells, while unlocked cells remain editable. A macro using Protect applies protection to the current state of those settings; it does not automatically decide which cells should be editable.

The three arguments in the sample make the intent explicit: Contents:=True protects cell contents, DrawingObjects:=True protects objects such as shapes, and Scenarios:=True protects scenarios. These are the standard protection categories. You can also use optional arguments to permit selected actions, such as filtering, sorting, or formatting, but allowances should match what users need to do.

For example, if a worksheet contains a calculation table, you might leave input cells unlocked and keep formula cells locked. Users can then enter new values without overwriting the formulas. Test the actual workflow after protecting the sheet: a permission that sounds suitable may still prevent a user from completing a task that depends on selecting, formatting, or sorting cells.

Choose which cells users can edit

To unlock cells manually, select them before protecting the worksheet. In Excel for Windows, press Ctrl+1 to open Format Cells, select the Protection tab, clear the Locked checkbox, and choose OK. Then run the protection macro. On Mac, use the Format Cells dialog and its Protection tab; the exact keyboard shortcut can vary with the keyboard configuration. The Review tab also provides the Protect Sheet command for setting protection without VBA.

Here is a small example for a form where users should edit cells B2:B10 but leave the rest of the sheet locked. The macro first locks every cell, unlocks the input range, and then protects the sheet:

Sub ProtectFormInputs()
    Dim ws As Worksheet
    Dim sheetPassword As String

    sheetPassword = "ChangeThisPassword"
    Set ws = ThisWorkbook.Worksheets("Form")

    ' Lock all cells, then unlock the user input range.
    ws.Cells.Locked = True
    ws.Range("B2:B10").Locked = False

    ws.Protect Password:=sheetPassword, _
        DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Change Form and B2:B10 to match the worksheet name and input cells in your workbook. This example resets the Locked setting for every cell on that sheet, so avoid running it if you already have a carefully designed pattern of unlocked cells elsewhere. A worksheet can also use Review > Allow Edit Ranges to define ranges that users may edit, optionally with separate passwords; that feature is useful when permissions need more structure than a single unlocked input area.

Allow useful actions while keeping cells protected

Protection does not have to mean that every action is blocked. The Protect method supports options that allow particular operations, including using AutoFilter, sorting, formatting cells, and inserting rows. For example, adding AllowFiltering:=True lets users change filter criteria on an existing AutoFilter. It does not create a filter for them, and it does not grant permission to make every other change.

Set only the allowances your users need. A report might allow filtering while keeping formulas and layout locked. A data-entry form might allow selection of unlocked cells but not formatting. If a permitted action does not work as expected, check whether the corresponding feature was already set up before protection and test with a representative user task. Some operations have additional requirements; allowing sorting, for instance, does not guarantee that every sort involving protected or mixed locked and unlocked ranges will work.

Optional arguments are named, so you can add one to the call without filling in every argument between it and the password. For instance, the following line is a valid way to allow filtering while protecting the sheet:

ws.Protect Password:=sheetPassword, Contents:=True, AllowFiltering:=True

Protecting and unprotecting sheets with VBA

To remove protection with VBA, call Unprotect and supply the same password. Use the worksheet name explicitly when the macro should target a particular tab rather than whichever sheet happens to be active.

Sub UnprotectDataSheet()
    Dim ws As Worksheet
    Dim sheetPassword As String

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

If the password is incorrect, Excel cannot use that call to unprotect the sheet and may report an error. A practical macro should therefore be tested on a backup copy, especially if it processes several sheets. Avoid putting a password prompt or error-handling routine into a production process unless you have decided how failed passwords and partially processed workbooks should be handled.

To protect every worksheet in the workbook, a loop can call Protect for each item in ThisWorkbook.Worksheets. First decide whether every sheet should use the same password and protection options. Different sheets often serve different purposes: a cover sheet, input form, and calculation sheet may need different editing permissions. A blanket loop is convenient, but it can also protect a sheet that users need to update.

Using UserInterfaceOnly for macros

Sometimes people should be blocked from changing locked cells while a separate macro still needs to update them. The UserInterfaceOnly:=True option is designed for that distinction: it applies protection to the user interface while allowing VBA code to make changes. For example, a routine might update a locked timestamp or refresh a controlled output area.

Excel does not retain the UserInterfaceOnly setting when a workbook is closed. Reapply it when the workbook opens by placing an event procedure in the ThisWorkbook module, not in a standard module. In the Visual Basic Editor, double-click ThisWorkbook in the project list and add code such as:

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim sheetPassword As String

    sheetPassword = "ChangeThisPassword"
    Set ws = ThisWorkbook.Worksheets("Data")

    ws.Protect Password:=sheetPassword, _
        Contents:=True, UserInterfaceOnly:=True
End Sub

The sheet must be protected with the relevant password and settings for this setup to work as intended. Save the workbook as .xlsm, close it, reopen it with macros enabled, and test both user editing and the macro that needs to change cells. If macros are disabled, the open event will not run, so users may encounter different behavior than expected.

Worksheet protection is not file encryption

A sheet password restricts editing through Excel’s normal worksheet interface; it does not conceal the sheet’s data. A user may be able to copy visible values, inspect workbook contents, or use other methods to get around worksheet protection. Microsoft describes worksheet protection as a way to prevent modification of cells, not as a security feature for sensitive information. Do not rely on it to protect personal, financial, confidential, or regulated data from access.

If the goal is to prevent people from opening the workbook, use file-level encryption instead. In desktop Excel, the Windows path is File > Info > Protect Workbook > Encrypt with Password. This is separate from protecting an individual sheet or the workbook’s structure. Keep an encryption password somewhere secure: losing it can prevent access to the file. If the aim is simply to stop accidental edits, worksheet protection is usually more appropriate and less disruptive.

Workbook structure protection is another distinct option. It limits actions such as adding, deleting, moving, hiding, or renaming sheets; it does not replace protecting the contents of each sheet. Choose the protection level based on the problem you are trying to solve rather than assuming one password setting covers all forms of access and editing.

Common problems and safe handling

The macro is missing after saving: the workbook may have been saved as .xlsx, which does not retain VBA. Save as .xlsm instead.
The wrong tab is protected: the first example targets ActiveSheet. Select the intended tab first, or replace it with a named worksheet reference.
Users can still edit some cells: those cells may have been unlocked before protection. Check Format Cells > Protection and the sheet’s locked-cell settings.
The macro will not run: check that you are using desktop Excel, that the code is in a standard module, and that your organization’s macro settings permit the file to run. Do not weaken security settings for an untrusted workbook.
The password appears in the code: anyone able to inspect the VBA project may be able to find a hard-coded password. VBA project protection is not a substitute for file encryption or secure access controls.

Keep a record of passwords according to your organization’s approved process, and test the workbook with a copy before distributing it. Worksheet-protection passwords are not a reliable recovery mechanism: if a password is lost, there may be no supported way to recover the intended protection settings. A macro also cannot be reversed with Ctrl+Z, so retain an unmodified backup when changing protection across important sheets.

Frequently Asked Questions

What is the simplest VBA line to protect a sheet?
With a worksheet object assigned to ws and a password string assigned to sheetPassword, use ws.Protect Password:=sheetPassword. Add optional arguments when you need to specify what users may do.

Can a macro protect a sheet without a password?
Yes. You can call ws.Protect without the Password argument to apply worksheet protection without a password. That may prevent routine edits, but users can remove that protection without supplying a password.

Does protecting a sheet protect the entire workbook?
No. Worksheet protection applies to a sheet’s editing permissions. Use workbook structure protection to restrict sheet changes, and file encryption if the requirement is to restrict opening the workbook.

Can I still edit a protected sheet with another macro?
Yes. One option is to unprotect the sheet in the macro, make the changes, and protect it again. Another is to apply protection with UserInterfaceOnly:=True and reapply that setting when the workbook opens.

Can I run the macro in Excel for the web?
No. Excel for the web does not run VBA. Use the desktop Excel app for Windows or Mac to add and run the macro; the protected workbook can still be opened in the web app, but VBA will not execute there.

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.