Fix Excel VBA Error 1004 on a Protected Worksheet
Question details
The user needs to prevent Excel VBA run-time error 1004 when a macro records the current date and time in cells on a protected worksheet.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro must write a date and time to a locked cell while the worksheet remains protected for regular users.
- Observed behavior
- Excel raises run-time error 1004 because the macro attempts to modify a protected worksheet or locked cell.
Confirm the worksheet name, target cell, and protection password used by the workbook. Open the VBA editor with Alt+F11 and place the procedure in the correct worksheet or standard module.
Unprotect the worksheet before the macro writes the date and time
Use this approach when the macro needs to update a locked cell while preserving worksheet protection for users afterward.
The macro must remove worksheet protection before changing a locked cell. It then protects the worksheet again after the update, using the same password configured for the sheet.
In Excel, press Alt+F11, choose Insert > Module, and open the module containing the macro that records the date and time.
Use code similar to the following, replacing the worksheet name, target cell, and password with the values in your workbook: Sub StampDateTime()\n Const PWD As String = "YourPassword"\n Dim ws As Worksheet\n Set ws = ThisWorkbook.Worksheets("Sheet1")\n On Error GoTo ErrorHandler\n ws.Unprotect Password:=PWD\n ws.Range("A1").Value = Now\n ws.Protect Password:=PWD\n Exit Sub\nErrorHandler:\n On Error Resume Next\n ws.Protect Password:=PWD\n MsgBox "The date and time could not be recorded: " & Err.Description\nEnd Sub
Return to Excel, press Alt+F8, select StampDateTime, and click Run. Confirm that the date and time appears in the target cell and that the worksheet is protected again.
Allow macros to edit a protected worksheet with UserInterfaceOnly
Use this option when the macro runs repeatedly and users should be prevented from editing protected cells without requiring the sheet to be visibly unprotected.
Use WPS Office as a Lightweight Alternative for Protected Spreadsheet Work
WPS Office provides a free, lightweight spreadsheet environment with a familiar interface and strong Microsoft Excel file compatibility. It can help users open, review, edit, and migrate spreadsheet content, although Excel-specific VBA macros and protected-sheet automation may require Microsoft Excel for full execution.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the Start menu or Applications folder.
- 2. Open the Excel workbook: Choose Open from the home screen and select the .xlsx, .xls, or .xlsm workbook you want to review or edit.
- 3. Edit and save a compatible copy: Update supported worksheet content in WPS Spreadsheets, then use File > Save As to choose an Excel-compatible format such as .xlsx.

Frequently Asked Questions
Why does Excel VBA Error 1004 occur on a protected worksheet?
The error commonly occurs when a macro tries to write to a locked cell while the worksheet is protected. Unprotect the worksheet before the write operation, or protect it with UserInterfaceOnly:=True so VBA can edit protected cells.
Where should the worksheet password be specified in the macro?
Add the password to both the Unprotect and Protect commands, for example ws.Unprotect Password:="YourPassword" and ws.Protect Password:="YourPassword". The password must match the one used to protect the worksheet.
Can a macro update a protected cell without showing the worksheet as unprotected?
Yes. Use Worksheets("Sheet1").Protect Password:="YourPassword", UserInterfaceOnly:=True in the Workbook_Open event. This lets VBA modify protected cells while preventing normal users from editing them.
What should I do if the worksheet has no password?
Remove the Password argument from the Unprotect and Protect commands, or use an empty password consistently. Verify the sheet's protection settings before running the macro.
Can WPS Office run this Excel VBA macro?
WPS Office can open many Excel workbooks and supports common spreadsheet formats, but Excel-specific VBA behavior may not run identically. Use Microsoft Excel when the protected-sheet macro must execute reliably.




