Automatically Add an Excel Timestamp in WPS Spreadsheets When a Dropdown Changes
Question details
The user needs a VBA Worksheet_Change event that automatically records the current date and time in column Q whenever a dropdown value in column P changes, specifically for rows 2 through 670.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet has dropdown selections in column P, and the user wants to log when each selection changes without manually entering the timestamp.
- Observed behavior
- Changing the dropdown in column P does not automatically create a timestamp in column Q; the timestamp must be inserted manually or with a formula that recalculates.
Before editing the macro, open the workbook in WPS Spreadsheets and save it as a macro-enabled workbook (.xlsm) so the VBA code is retained. Enable the Developer tab from File > Options > Customize Ribbon if you need to open the Visual Basic Editor from the ribbon.
Use a Worksheet_Change VBA Event to Write Now to Column Q When Column P Changes
Use this when you need a permanent, automatic timestamp in Q2:Q670 based on dropdown edits in P2:P670.
Place the code in the worksheet module that contains the dropdowns, not in a standard module. The Worksheet_Change event fires after a cell value is changed, and the Intersect check restricts it to P2:P670. The code temporarily disables events while writing the timestamp so the timestamp itself does not trigger the same event.
Right-click the sheet tab that contains the dropdown column and select View Code. This opens the VBA editor with the worksheet module active.
Copy and paste the following code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) Dim changed As Range Set changed = Intersect(Target, Me.Range("P2:P670")) If changed Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Application.ScreenUpdating = False changed.Offset(0, 1).Value = Now CleanUp: Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Click File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and save the file. Avoid saving as .xlsx because it will discard the macro.
Change a dropdown value in column P between rows 2 and 670. The adjacent cell in column Q should immediately show the current date and time.
Automatically Add Timestamps in WPS Spreadsheets with Worksheet_Change VBA
Use the WPS Spreadsheets VBA editor to place a Worksheet_Change macro in the worksheet module. It records the current date and time in column Q whenever a dropdown in column P changes, matching the Excel VBA workflow.
- 1. Open your macro-enabled workbook: In WPS Spreadsheets, open the workbook that contains the dropdown column and save it as an Excel Macro-Enabled Workbook (*.xlsm) if it is not already saved in that format.
- 2. Open the Visual Basic Editor: Click the Developer tab and select Visual Basic Editor. If the Developer tab is not visible, enable it from File > Options > Customize Ribbon.
- 3. Paste the code into the worksheet module: In Project Explorer, double-click the sheet name that has the dropdowns, paste the Worksheet_Change code into the worksheet module, and adjust P2:P670 if needed.
- 4. Test the automatic timestamp: Change a dropdown value in column P and confirm that the adjacent cell in column Q receives the current date and time.
Frequently Asked Questions
Why is the timestamp macro not running when I change the dropdown?
Make sure the code is inside the worksheet module for the sheet that contains column P, not a standard module. Also confirm that macros are enabled and the workbook is saved as an .xlsm file.
Can I use this macro for a different column or row range?
Yes. Modify the range in Me.Range("P2:P670") to your target range. The adjacent timestamp is controlled by Changed.Offset(0, 1), so keeping Offset(0, 1) writes one column to the right.
Will the timestamp stay fixed, or will it update like the NOW() formula?
The VBA code writes the current date and time as a static value with Now, so it remains fixed after it is inserted. Avoid using =NOW() as a cell formula if you need a permanent audit timestamp.
Does WPS Spreadsheets support the same Worksheet_Change VBA event as Excel?
WPS Spreadsheets supports VBA macros and Worksheet_Change events, and it can open Excel macro-enabled workbooks. Some advanced VBA features may differ by version, so test the macro in your WPS build.
