VBA & Macro Problems

Fix Excel VBA Error 1004 on a Protected Worksheet

Elise WilliamsElise Williams Nov 12, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, press Alt+F11, choose Insert > Module, and open the module containing the macro that records the date and time.

2
Add the unprotect and protect commands

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

3
Run and verify the macro

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.

Password must match: If the worksheet has no password, remove the Password argument or use an empty password. A different password causes the Unprotect command to fail and the cell will remain unchanged.
Free Microsoft Office alternative

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. 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the Start menu or Applications folder.
  2. 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. 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.
Open and edit Microsoft Excel .xlsx and .xls files with a familiar spreadsheet interface.Work with protected spreadsheet layouts and formulas without the overhead of a full Microsoft Office installation.Migrate common Excel worksheets and formatting smoothly to WPS Spreadsheets.Use a lightweight free office suite for reviewing macro-enabled workbooks when macro execution is not required.
microsoft office alternative - wps office

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.