How to Track the Last Modified Date of an Excel Cell in WPS Office
Question details
The user wants to automatically record the date and time when a cell is edited, for example when a drop-down equipment status changes.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook has status cells that need a last-modified timestamp whenever a user changes a value.
- Observed behavior
- The goal is to have the current date and time automatically written into a timestamp column when a cell in a monitored range changes.
Open the workbook that contains the status cells and verify that macro content is enabled. If the workbook is not already macro-enabled, save it as an Excel Macro-Enabled Workbook (.xlsm) before adding the VBA code.
Add a Worksheet_Change Macro to Timestamp a Modified Cell
Use this VBA event when you need WPS Spreadsheets or Excel to automatically record the last modified date and time for a cell, such as a drop-down equipment status.
The Worksheet_Change event runs after a user changes any cell. Place the code in the specific worksheet module so WPS/Excel only checks the range you define and writes the current date and time to the timestamp column.
In WPS Spreadsheets or Excel, press Alt+F11 to open the VBA editor. In Project Explorer, double-click the worksheet name where the status drop-down is located.
Paste the following macro into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Range("A1:A100") If Not Intersect(Target, rng) Is Nothing Then Application.EnableEvents = False Cells(Target.Row, "B").Value = Now Application.EnableEvents = True End If End Sub
Change A1:A100 to the range you want to monitor, and change "B" to the column where the date and time should be written.
Save the file as an Excel Macro-Enabled Workbook (.xlsm), close the VBA editor, and then change a value in the monitored range. The timestamp column should update with the current date and time.
Track Last Modified Dates Automatically in WPS Spreadsheets
Create a small VBA event in WPS Spreadsheets to time-stamp cell changes. WPS Office supports macro-enabled Excel workbooks, so you can monitor equipment status changes and record the date and time without switching tools.
- 1. Open the workbook in WPS Spreadsheets: Open the target workbook and make sure macros are enabled from the WPS Security or Macro Settings options.
- 2. Open the VBA editor: Press Alt+F11 to open the VBA editor, then double-click the worksheet you want to track under Microsoft Excel Objects in the Project Explorer.
- 3. Paste the timestamp macro: Insert the Worksheet_Change code and set the monitored range and timestamp column, such as Range("A1:A100") and column "B".
- 4. Save and test the workbook: Save the workbook as .xlsm, close the editor, and edit a monitored cell. The current date and time should appear in the timestamp column.
Frequently Asked Questions
Where should I put the VBA code to track a cell change?
The code belongs in the worksheet module, not a standard module. In the VBA editor, expand Microsoft Excel Objects, right-click the target sheet, select View Code, and paste the Worksheet_Change macro there.
Why is the timestamp not updated after I edit the cell?
Check that macros are enabled, the workbook is saved as .xlsm, the monitored range matches your data, and the timestamp column is correct. If you changed the sheet name, the code must still be in that sheet's module.
Can I track changes in several columns and write timestamps to different columns?
Yes. You can add multiple range checks inside the Worksheet_Change event, for example If Not Intersect(Target, Range("A1:A100")) Is Nothing Then Cells(Target.Row, "B").Value = Now, and another range/column pair for other status columns.
Does this solution work when I choose an item from a data validation drop-down list?
Yes. The Worksheet_Change event fires after a drop-down selection, so the selected cell is updated and the timestamp is written immediately.
Can I format the timestamp to show only the date or a specific time format?
Yes. Apply a number format to the timestamp column, such as yyyy-mm-dd or yyyy-mm-dd hh:mm:ss, or change the VBA value from Now to Date if you only need the date.
