Automatically Enter the Current Date When a Cell Changes in WPS Spreadsheets
Question details
The user wants to use a worksheet-level VBA event in WPS Spreadsheets to automatically enter the current date in cell A1 when initials are entered or changed in cell A2, clear A1 when A2 is emptied, and optionally include the time.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is setting up a workbook that needs a timestamp in A1 whenever a colleague enters initials in A2.
- Observed behavior
- The timestamp cell does not update automatically when A2 changes; it must be entered manually or is left blank.
Make sure the workbook is saved as a macro-enabled file type (.xlsm) and that macros are enabled in WPS Spreadsheets. Open the VBA editor from the specific worksheet tab you want to modify so the event code is placed in the correct module.
Use a Worksheet_Change Macro to Date-Stamp A1 When A2 Changes
Use this when you need WPS Spreadsheets to automatically record the current date in A1 after initials are entered or changed in A2, and clear it when A2 is emptied.
The Worksheet_Change event runs whenever a cell on the sheet is changed. The macro checks whether the changed range intersects A2, temporarily disables events to avoid recursive calls, then writes the date or clears A1.
Right-click the worksheet tab where you want the date stamp, and choose View Code from the context menu.
In the VBA editor, paste the following code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A2")) Is Nothing Then On Error GoTo CleanUp Application.EnableEvents = False If Me.Range("A2").Value = "" Then Me.Range("A1").ClearContents Else Me.Range("A1").Value = Date End If CleanUp: Application.EnableEvents = True End If End Sub
Go to File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm) in WPS Spreadsheets, and save the file.
Reopen the workbook and click Enable Content or Enable Macros when prompted. Type initials in A2 and confirm A1 shows the current date; delete A2 and confirm A1 is cleared.
Set Up Automatic Date Stamping in WPS Spreadsheets with a VBA Macro
Use the Worksheet_Change event in WPS Spreadsheets to automatically write the current date in A1 when A2 changes. The macro clears A1 when A2 is empty and can be switched from Date to Now for a full timestamp.
- 1. Open the worksheet VBA editor: Right-click the WPS Spreadsheets sheet tab and select View Code.
- 2. Insert the Worksheet_Change macro: Paste the provided VBA code into the worksheet module so it checks Target against cell A2 and updates A1.
- 3. Save and enable macros: Choose File > Save As and save as Excel Macro-Enabled Workbook (*.xlsm), then enable macros when reopening.
- 4. Test the date stamp: Type initials in A2 and confirm A1 updates to the current date. Clear A2 and confirm A1 is cleared.
- 5. Optional time stamp: Change Date to Now in the macro if you need both the current date and time.
Frequently Asked Questions
Does this macro work in WPS Spreadsheets the same as in Excel?
Yes, WPS Spreadsheets supports the Worksheet_Change event and macro-enabled .xlsm workbooks. Right-click the sheet tab and choose View Code to place the macro in the worksheet module.
How do I record both date and time instead of only the date?
Replace Date with Now in the line Me.Range("A1").Value = Date. This stamps cell A1 with both the current date and time.
Why does nothing happen after I paste the macro?
Check that the file is saved as a macro-enabled workbook (.xlsm), that macros are enabled, and that the code is in the worksheet module for the specific sheet, not a standard module.
Can I change the target cells from A2 and A1?
Yes, replace Me.Range("A2") and Me.Range("A1") with the cell references you want to monitor and stamp.
Will A1 update every time A2 changes or only when A2 is first filled?
With the provided code, A1 updates every time A2 changes and clears when A2 is emptied. To only stamp the first entry, add a condition that exits when A1 already contains a value.
