Excel VBA Protect Sheet Allow Format Cells

VBA & macros · Updated November 22, 2026

Yes, you can protect a worksheet in Excel VBA while still allowing users to format cells. Use the Protect method with the AllowFormattingCells argument set to True: Worksheets("Sheet1").Protect Password:="secret", AllowFormattingCells:=True. This lets users change fonts, colors, borders, and number formats on unlocked cells (and locked cells if you also set AllowFormattingColumns and AllowFormattingRows, though that is a separate setting).

What the Protect Method Actually Controls

When you protect a sheet in Excel, every cell is locked by default, but locking only has an effect once protection is turned on. The Protect method in VBA accepts a series of optional arguments that define exactly what users can and cannot do. The relevant one for formatting is AllowFormattingCells, which permits users to change the formatting of cells without unlocking them. This includes font style, size, color, fill color, borders, alignment, and number format.

Here is the full syntax for the Protect method (all arguments are optional):

Worksheets("Sheet1").Protect Password:="password", _
    DrawingObjects:=True, Contents:=True, Scenarios:=True, _
    UserInterfaceOnly:=False, AllowFormattingCells:=True, _
    AllowFormattingColumns:=False, AllowFormattingRows:=False, _
    AllowInsertingColumns:=False, AllowInsertingRows:=False, _
    AllowDeletingColumns:=False, AllowDeletingRows:=False, _
    AllowSorting:=False, AllowFiltering:=False, _
    AllowUsingPivotTables:=False

Note that AllowFormattingCells only affects formatting of cells; it does not allow users to change the cell contents. If you want users to change column width or row height, you need AllowFormattingColumns and AllowFormattingRows respectively.

Why You Might Need This

In many real-world workbooks, you want to protect formulas or critical data from being accidentally overwritten, but still allow users to visually customize the sheet—for example, highlighting a row with a fill color, bolding a heading, or adjusting the number format for a specific range. Without AllowFormattingCells:=True, any attempt to format a protected cell results in an error message: “The cell or chart you are trying to change is protected and therefore read-only.”

A common scenario is a budget template where the input cells are unlocked, but the formulas are locked. You want users to be able to change the font or color of any cell (including formula cells) to suit their report, but not edit the formula itself. Setting AllowFormattingCells:=True achieves that.

How to Use It in VBA: Step by Step

Here is a complete macro that protects a sheet while allowing cell formatting. It also unprotects first, in case the sheet is already protected.

Sub ProtectAllowFormatting()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Unprotect first to avoid errors if already protected
    ws.Unprotect Password:="password"
    
    ' Protect with formatting allowed
    ws.Protect Password:="password", _
        AllowFormattingCells:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True
    
    MsgBox "Sheet protected. Formatting is allowed."
End Sub

Save a copy of your workbook before running this macro, because macros cannot be undone with Ctrl+Z. If you forget the password, you will be locked out of unprotecting the sheet.

Choosing Between AllowFormattingCells, Columns, and Rows

Excel’s protection options distinguish between formatting cells (the inside of the cell), columns (width and hide/unhide), and rows (height and hide/unhide). You can enable any combination. Here is a quick reference:

  • AllowFormattingCells: change font, fill, borders, alignment, number format of cells.
  • AllowFormattingColumns: change column width and hide/unhide columns.
  • AllowFormattingRows: change row height and hide/unhide rows.

If you only set AllowFormattingCells:=True, users cannot resize columns or rows. If you want full formatting freedom, set all three to True. In the macro above, we set all three.

Common Problems and Misconceptions

Misconception: “Formatting cells is allowed, so users can edit the values.” This is false. Formatting permissions are independent of editing permissions. Even if AllowFormattingCells is True, users still cannot change the content of locked cells unless the cell is unlocked before protection.

Problem: The macro doesn’t work because the sheet is already protected with a different password. The Unprotect line will fail if the password is wrong. Use On Error Resume Next or check with ws.ProtectContents property.

Problem: Users can format but not edit, yet they want to change a value. That requires unlocking the specific cells before protecting. You can do that in VBA by setting ws.Cells.Locked = False for the input range before calling Protect.

Problem: The protection doesn’t apply to shapes or charts. The DrawingObjects argument controls that. If you want to protect shapes, leave it True (default).

Comparison: Protect Sheet vs. Protect Workbook

It is easy to confuse sheet protection with workbook protection. Sheet protection (what we’ve been discussing) restricts what users can do on a specific worksheet. Workbook protection (via ThisWorkbook.Protect) restricts structural changes like adding, deleting, moving, or hiding sheets. They are independent. You can protect a sheet but not the workbook, and vice versa. If you want to prevent users from unprotecting the sheet via the ribbon, you might also protect the workbook structure with a password, but that is a separate step.

Alternatives to VBA: Using the Ribbon

You don’t need VBA to allow formatting on a protected sheet—you can do it manually. Go to Review > Protect Sheet, then in the dialog box, check the box for Format cells (and optionally Format columns and Format rows). This is the same as setting the VBA arguments. The VBA method is useful when you need to apply the same protection to many sheets or workbooks, or when you want to toggle protection automatically.

Frequently Asked Questions

How do I allow formatting only on unlocked cells?

By default, AllowFormattingCells:=True applies to all cells, locked or unlocked. To restrict formatting to only unlocked cells, you would need to use a different approach—for example, using a Worksheet_SelectionChange event to check if the selected cell is locked before allowing formatting. However, that is complex and not built-in. In practice, most users allow formatting on all cells because it does not affect data integrity.

Can I allow formatting but not allow users to unprotect the sheet?

Yes, if you set a password and do not give it out, users cannot unprotect the sheet through the UI. However, a determined user can find the password if it’s stored in the VBA code. For better security, you can protect the VBA project with a password, but that is not foolproof.

Why does my macro error with “Cannot use this command on a protected sheet”?

That error usually occurs when you try to modify a cell’s value or structure on a protected sheet. Formatting is allowed if you set AllowFormattingCells:=True, but editing values is not. If you need to edit values programmatically, you must unprotect the sheet first, make changes, then re-protect.

Does UserInterfaceOnly:=True affect formatting?

No. UserInterfaceOnly:=True allows VBA macros to modify the sheet while protection is on, but it does not affect what the user can do via the UI. It is a separate argument that is useful if you want to allow macros to change values without unprotecting. Formatting permissions are still controlled by the AllowFormatting* arguments.

Can I allow formatting on a protected sheet in Excel for the web or Mac?

Yes, the Protect method works the same in Excel for Microsoft 365 on Windows, Mac, and Excel for the web, though the ribbon path is slightly different on Mac (Review > Protect Sheet). The VBA code is identical. However, VBA is not supported in Excel for the web, so you would need to use the manual ribbon method there.

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.