Fix Excel Error 1004 When Changing NumberFormat on a Protected Sheet
Question details
The user needs to apply a timestamp and number format with VBA on a protected Excel worksheet.

- 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.
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.
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.
In Excel, press Alt+F11 and open the module or button-click procedure that writes the timestamp.
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
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.

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.

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.




