How to Update Locked Date and Time Cells Using Excel VBA Macros
Question details
The user wants a worksheet-change VBA macro to automatically insert date and time stamps into specific cells without allowing manual user edits on those locked cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating timestamp entries in a protected worksheet where the target cells are locked to prevent user manipulation.
- Observed behavior
- By default, VBA macros cannot write data to locked cells in a protected worksheet, causing a run-time error unless the protection is temporarily handled by the script.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you know the exact password currently used to protect the worksheet.
Temporarily Unprotect and Reprotect the Worksheet in VBA
Modify your Worksheet_Change macro to unprotect the sheet before writing the timestamp, and then protect it again immediately after.
By default, VBA macros cannot modify locked cells in a protected worksheet. To bypass this security restriction, the macro must briefly remove the protection using the sheet's password, update the target cells with the current date and time, and immediately re-apply the protection.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor and locate the specific worksheet module where your change event macro is written.
At the beginning of your Worksheet_Change event, add the command `Me.Unprotect Password:="YourPassword"` to remove the sheet protection temporarily.
Add your logic to write the date and time to the locked cells. For example: `Range("E" & Target.Row).Value = Date`.
At the end of your macro logic, add the command `Me.Protect Password:="YourPassword"` to lock the cells and secure the worksheet again.
Run VBA Macros Seamlessly in WPS Spreadsheet
WPS Office provides robust VBA and Macro support, allowing you to automate timestamping and manage worksheet protection just like you do in Microsoft Excel, but within a lightweight and completely free environment.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the worksheet protection macros.
- 3. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'VBA Editor' to view, edit, and run your automated timestamp scripts.
Frequently Asked Questions
Why do I get a "1004" run-time error when my macro tries to write to a locked cell?
This error occurs because the worksheet is actively protected. You must add the `Unprotect` method in your VBA code before the script attempts to modify any locked cells, and reprotect it afterward.
Can I allow VBA to run on protected sheets without unprotecting them every time?
Yes, you can use the command `Sheet1.Protect Password:="YourPassword", UserInterfaceOnly:=True` inside the `Workbook_Open` event. This special protection setting allows macros to modify locked cells while continuing to restrict manual user edits.
How do I ensure my worksheet stays protected if the VBA macro encounters an error?
You should use an error-handling routine in your code. Add `On Error GoTo ErrorHandler` at the top of your macro, and under the `ErrorHandler:` label at the bottom, include the `Me.Protect Password:="YourPassword"` command to ensure the sheet locks even if the script fails.
