Excel Error Codes

Fix Excel Error 1004 When Changing NumberFormat on a Protected Sheet

Elise WilliamsElise Williams Nov 22, 2026 868 views

Question details

The user needs to apply a timestamp and number format with VBA on a protected Excel worksheet.

Fix Excel Error 1004 When Changing NumberFormat on a Protected Sheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
A VBA procedure writes a timestamp to an unlocked cell and applies a time format while the worksheet is protected.
Observed behavior
Excel raises run-time error 1004 when the macro attempts to change the cell's NumberFormat, even though the cell is unlocked.
Before you start

Confirm that you know the worksheet protection password, if one is required, and identify the worksheet's VBA code name in the Visual Basic Editor. Check the sheet's protection permissions before changing them, because reprotecting it may reset custom permissions.

Solution 1Recommended

Unprotect the worksheet before applying the number format

Use this workaround when a macro must format an unlocked cell on a protected worksheet.

An unlocked cell can be edited on a protected sheet, but changing its format may still be blocked. The macro below temporarily unprotects the sheet and attempts to restore protection afterward.

Replace Sheet1 with the worksheet's VBA code name and replace the password placeholder. Use an empty string if the sheet has no password. VBA passwords stored in code are visible to anyone who can inspect the project.

1
Open the VBA editor

In Excel, press Alt+F11 and open the module or button-click procedure that writes the timestamp.

2
Update the procedure

Replace the relevant procedure with the following example, adjusting the procedure name, sheet code name, and password as needed: Public Sub WriteTimestamp() Dim ws As Worksheet Dim target As Range Dim wasProtected As Boolean Dim pwd As String Set ws = Sheet1 pwd = "your_password" If TypeName(Selection) <> "Range" Then Exit Sub Set target = Selection If Not target.Worksheet Is ws Then Exit Sub If target.CountLarge <> 1 Then Exit Sub If target.Locked Then Exit Sub wasProtected = ws.ProtectContents On Error GoTo Failed If wasProtected Then ws.Unprotect Password:=pwd target.Value = Now target.NumberFormat = "h:mm AM/PM" If wasProtected Then ws.Protect Password:=pwd Exit Sub Failed: MsgBox "The timestamp could not be completed: " & Err.Description If wasProtected Then On Error Resume Next ws.Protect Password:=pwd If Err.Number <> 0 Then MsgBox "Could not restore sheet protection. Check the sheet manually." On Error GoTo 0 End If End Sub

3
Test the macro

Select one unlocked cell on the target sheet, then run the procedure from Developer > Macros or use the button assigned to it. Confirm that the timestamp appears in the intended format and that worksheet protection is restored.

Unprotect the worksheet before applying the number format
Preserve custom protection settings: The example uses the default Protect settings. If the sheet allows specific actions or uses other protection options, supply the appropriate arguments to Protect when restoring it.
Free Microsoft Office alternative

Work with spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and move everyday spreadsheet work to WPS; it does not repair Excel's VBA error.

Format cells and display timestamps in WPS SpreadsheetsOpen and edit compatible Microsoft Excel XLS and XLSX filesUse a familiar interface for everyday spreadsheet tasksMove existing Office documents into a lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does NumberFormat fail when the cell is unlocked?

Unlocking a cell permits editing its contents on a protected sheet, but formatting can still be restricted. Temporarily unprotect the sheet before setting NumberFormat, or review the formatting permissions under Review > Protect Sheet.

How can I check whether a cell is locked?

Select the cell, open Format Cells, and check the Locked option on the Protection tab. The setting takes effect when the worksheet is protected.

What if the worksheet has no protection password?

Use an empty string for the password in the example, such as pwd = "". The macro can then unprotect and reprotect the sheet without a password.

Will the macro retain all existing sheet protection options?

Not automatically. The sample calls Protect with its default options; add the needed Protect arguments to restore any custom permissions used by the worksheet.