How to Keep an Excel Worksheet’s Last-Modified Date from Changing on Open in WPS Office
Question details
The user needs a VBA timestamp in cell B5 to update only when the worksheet changes and to remain unchanged when the workbook opens without edits.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has an Excel-compatible workbook with a VBA-managed last-modified timestamp in cell B5 and wants to prevent opening the file from refreshing that timestamp when no edits are made.
- Observed behavior
- The timestamp in cell B5 remains unchanged when the workbook opens without edits and updates only after a worksheet change.
Open the workbook in WPS Office Spreadsheets and press Alt+F11 to open the VBA editor. Locate the worksheet module that contains cell B5, and review the ThisWorkbook and worksheet modules for existing Workbook_Open or Worksheet_Activate code before adding new VBA.
Use the Worksheet_Change Event to Update the Timestamp Only on Real Edits
Use this when you want cell B5 to stay unchanged during workbook open and update only after a worksheet cell is changed.
Worksheet_Change runs only when cells on that sheet change, not when the workbook opens. Disabling events while writing the timestamp prevents the change event from re-triggering itself.
Open the workbook in WPS Office Spreadsheets, press Alt+F11, and select the worksheet module for the sheet that contains cell B5 in Project Explorer.
In the worksheet module, paste the following procedure. It checks that the changed range intersects the used range, disables events before writing to B5, writes the current time, and re-enables events even if an error occurs: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.UsedRange) Is Nothing Then Application.EnableEvents = False On Error GoTo CleanExit Range("B5").Value = Now CleanExit: Application.EnableEvents = True End If End Sub
If another process intentionally changes B5 and that change should not refresh the timestamp, add a condition to ignore B5, for example by checking If Not Intersect(Target, Range("B5")) Is Nothing Then Exit Sub before writing to B5.
Save the workbook as a macro-enabled file, close it, reopen it without editing, and confirm B5 remains unchanged. Then change any other cell in the used range and confirm B5 updates.
Check for Other Workbook_Open or Worksheet_Activate Macros That Modify Cells
Use this if the timestamp still changes when the workbook opens after applying the Worksheet_Change solution.
Keep Your Excel Last-Modified Date Stable on Open with WPS Office VBA
WPS Office Spreadsheets supports Excel-compatible VBA, so you can edit the Worksheet_Change event to update cell B5 only when users actually change the sheet. The timestamp remains unchanged when the workbook opens without edits.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets, open the macro-enabled workbook that contains the timestamp in cell B5, and enable macros if prompted.
- 2. Open the VBA editor: Press Alt+F11 to open the VBA editor and select the worksheet module for the sheet that contains cell B5.
- 3. Enter the Worksheet_Change timestamp code: Add the Worksheet_Change procedure that writes the current time to B5 only when a worksheet cell changes, and disable events while writing to avoid recursion.
- 4. Save and test the timestamp behavior: Save the workbook as a macro-enabled Excel file, close and reopen it without editing to confirm B5 remains unchanged, then edit a cell to confirm B5 updates.
Frequently Asked Questions
Why does the timestamp in B5 still change when I open the workbook?
Check ThisWorkbook > Workbook_Open and each sheet’s Worksheet_Activate code in the VBA editor. Any Range or Cells write to B5 at open will refresh the timestamp; move the update to Worksheet_Change or remove the open-time write.
Which VBA event should I use to update a cell only when the worksheet changes?
Use the Worksheet_Change event in the worksheet module for the sheet containing B5. It fires only when cells in that worksheet are changed, so workbook open without edits leaves the timestamp unchanged.
How do I open the VBA editor in WPS Office Spreadsheets?
Open the workbook in WPS Office Spreadsheets, press Alt+F11, and choose the target worksheet module in Project Explorer. If Project Explorer is hidden, show it from the View menu in the VBA editor.
Do I need to disable events in the timestamp macro?
Yes. Set Application.EnableEvents = False before writing the timestamp to B5 and set it back to True immediately after, including an On Error GoTo label, so a failed write does not leave events disabled.
Will WPS Office preserve my Excel VBA timestamp macro when saving?
Yes. Save the file as an Excel macro-enabled workbook (.xlsm) from WPS Office Spreadsheets. The VBA procedure and timestamp behavior are retained when reopened in WPS Office or Microsoft Excel.
