Update an Excel Date Automatically When Another Cell Changes in WPS Office
Question details
A user needs a WPS Excel date cell to refresh whenever another cell changes, rather than only recording the first entry; this requires a Worksheet_Change VBA macro.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has an input cell, such as C8, and wants a timestamp in another cell, such as J8, to update after every change.
- Observed behavior
- A standard Excel formula can record the first date that a cell is filled, but it does not permanently update the date when the same cell changes unless iterative calculation or VBA is used.
Open the workbook in WPS Spreadsheets, save a copy as an Excel Macro-Enabled Workbook (.xlsm), and make sure the Developer tab is visible so you can access the VBA editor.
Use a Worksheet_Change Event to Update the Date Automatically
Use this VBA method when you need the date in J8 to refresh every time the value in C8 changes.
The Worksheet_Change event fires every time a cell on the worksheet is edited. The code checks whether the changed cell intersects C8, temporarily disables Excel events to avoid re-triggering, writes the current date to J8, and then restores events.
Right-click the sheet tab that contains C8 and J8, then choose View Code or press Alt+F11 to open the VBA editor.
In the Project pane, double-click the worksheet object for the sheet where C8 and J8 are located so the code is pasted into the correct sheet module.
Copy and paste the following VBA code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("C8")) Is Nothing Then Application.EnableEvents = False Me.Range("J8").Value = Date Application.EnableEvents = True End If End Sub
Close the VBA editor and save the file in WPS Spreadsheets as an Excel Macro-Enabled Workbook (*.xlsm) so the VBA code is retained.
Use a Formula to Record Only the First Entry Date
Use this non-VBA method only when the date should be stamped once, the first time a cell is filled, and later changes should not alter it.
Automate Excel Date Updates with WPS Office VBA
Open your workbook in WPS Spreadsheets, insert a Worksheet_Change macro, and WPS Office will automatically write the current date to J8 whenever C8 changes while preserving Microsoft Excel macro compatibility.
- 1. Download and install WPS Office: Go to the official WPS Office download page and install WPS Spreadsheets for your operating system.
- 2. Open the timestamp workbook: Launch WPS Spreadsheets and open the .xlsm or .xlsx workbook containing the input cell C8 and timestamp cell J8.
- 3. Open the VBA editor: Right-click the worksheet tab and choose View Code to open the WPS VBA editor.
- 4. Add the Worksheet_Change macro: Paste the Worksheet_Change code in the worksheet module, then save as an Excel Macro-Enabled Workbook (*.xlsm).
- 5. Test the automatic date update: Type a value in C8 and confirm that J8 updates to the current date. Use Now instead of Date if you need the time as well.
Frequently Asked Questions
Why doesn’t a normal date formula update every time the cell changes?
A standard formula cannot permanently remember that it already stamped a date. It either updates with TODAY() on every recalculation or requires an iterative calculation setup. To force a permanent update after each edit, use the Worksheet_Change VBA macro.
How do I change the date cell from J8 to another cell?
In the VBA code, change the Range("C8") reference to your source cell and the Me.Range("J8").Value destination to the cell where you want the date written.
Can this macro record the time as well as the date?
Yes. Replace Date with Now in the line Me.Range("J8").Value = Date, then apply a date and time number format to the destination cell so it displays both values.
What file format should I save the workbook in to keep the VBA code?
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). The standard .xlsx format does not preserve VBA macros.
Where do I paste the Worksheet_Change code in WPS Office?
Right-click the worksheet tab, choose View Code, and paste the code into that worksheet module. Do not paste it into a regular module if you want the event to run specifically for that sheet.
