Runtime Error 1004: “The cell or chart you’re trying to change is on a protected sheet.”

Excel VBA raises Runtime Error 1004: “The cell or chart you’re trying to change is on a protected sheet” when a macro tries to write data, clear contents, format cells, or modify a chart on a worksheet with active protection. By default, worksheet protection blocks both manual user edits and programmatic macro edits.

Quick Answer

To resolve this error, either unlock the specific cells your macro needs to edit before protecting the sheet, temporarily unprotect the worksheet within your code using ws.Unprotect Password:="secret", or initialize sheet protection with UserInterfaceOnly:=True, which allows VBA to modify cells while keeping manual user edits blocked.

---

Why the Error Happens

Every cell in an Excel worksheet has a Locked property that defaults to True. This property does nothing until you protect the worksheet. Once protection is turned on:

1. Excel locks all cells where Locked = True.
2. Standard VBA commands like .Value = ..., .ClearContents, .Delete, and chart property updates are treated as unauthorized edits.
3. Excel interrupts execution and throws Runtime Error 1004 on the line attempting the write operation.

' This line fails if Sheet1 is protected and A1 is locked:
Worksheets("Sheet1").Range("A1").Value = "Updated"

If your macro fails with a non-descriptive error message on protected sheets, it may also manifest as the generic error described in Runtime Error 1004: Application-defined or Object-defined error (The Catch-all)..

---

First-Line Fix: UserInterfaceOnly Protection

The cleanest solution in VBA is the UserInterfaceOnly parameter of the Protect method. Setting this parameter to True tells Excel to block edits from the keyboard and mouse while allowing your macros to edit cells, rows, columns, and charts freely.

Add this routine to your workbook initialization (such as in Workbook_Open inside the ThisWorkbook module):

Private Sub Workbook_Open()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect Password:="AdminKey", _
                   UserInterfaceOnly:=True, _
                   DrawingObjects:=True, _
                   Contents:=True, _
                   Scenarios:=True
    Next ws
End Sub

Important Caveat: Excel does not persist the UserInterfaceOnly setting after the workbook closes. Each time the workbook opens, protection defaults back to blocking both users and macros unless you re-apply UserInterfaceOnly:=True via VBA.

---

What to Do If That Fails: Diagnostic Branches

If UserInterfaceOnly does not fit your workflow or fails to clear the error, check the following branches:

Branch A: Temporary Unprotect and Re-Protect

If your macro runs on ad-hoc files or legacy workbooks where Workbook_Open code is not practical, wrap the specific update block in Unprotect and Protect calls:

Sub UpdateSecureData()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Financials")

    On Error GoTo CleanExit
    ws.Unprotect Password:="AdminKey"

    ' Perform your operations safely
    ws.Range("B2:B10").Value = 0
    ws.ChartObjects("QuarterlyChart").Chart.ChartTitle.Text = "Q4 Results"

CleanExit:
    ws.Protect Password:="AdminKey"
    If Err.Number <> 0 Then
        MsgBox "Update failed: " & Err.Description, vbCritical
    End If
End Sub

Branch B: Selective Cell Unlocking (No Password Code)

If end users or automated processes must update only an input range, unlock those cells ahead of time:

1. Highlight the cells the macro modifies.
2. Press Ctrl + 1 to open the Format Cells dialog.
3. Go to the Protection tab and uncheck Locked.
4. Protect the worksheet normally.

Now, macros (and users) can update those cells without triggering Error 1004.

Branch C: Modifying Embedded Charts and Shapes

Modifying shapes, series data, or chart titles fails if the worksheet was protected with DrawingObjects:=True and without UserInterfaceOnly:=True. To unlock a chart without unprotecting the sheet:

1. Right-click the chart border and choose Format Chart Area.
2. Under Properties, uncheck Locked.

If your automation also needs to create new tabs or configure validation rules on protected files, see Runtime Error 1004: Errors when programmatically adding Data Validation. and #REF! in Protected Sheets: Missing Permissions for formula updates.