How to Automatically Insert a Date in WPS Spreadsheets When OK Is Entered
Question details
User needs a worksheet change macro that records the current date when OK is entered in column D and displays it in column E as text such as Paid on 10-10-2023.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A status or payment tracker uses column D to receive OK entries, and the user wants the paid date to be recorded automatically in the adjacent column without manual typing.
- Observed behavior
- The date is not automatically inserted; the expected result is a date value formatted with the custom prefix 'Paid on' in column E.
Open WPS Spreadsheets and make sure the Developer tab is available before you open the VBA editor. Confirm the worksheet where OK will be entered uses column D for status and column E for the paid date.
Use a Worksheet_Change Macro to Stamp the Date When OK Is Entered in Column D
Use this solution when you want WPS Spreadsheets to record the current date automatically in column E every time a cell in column D is updated to OK.
This solution uses the Worksheet_Change event in the worksheet code module. The macro reacts only to changes in column D, writes the current date into the adjacent cell when the value is OK, and clears it when the value changes. Disabling EnableEvents during the update prevents recursive event calls.
Select column E, right-click and choose Format Cells. On the Number tab, click Custom and enter "Paid on "dd-mm-yyyy, then click OK.
Right-click the worksheet tab where OK will be entered and click View Code to open the VBA editor for that worksheet module.
In the worksheet code module, paste the following macro: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Not Intersect(Range("D2:D" & Rows.Count), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each rng In Intersect(Range("D2:D" & Rows.Count), Target) If rng.Value = "OK" Then rng.Offset(0, 1).Value = Date Else rng.Offset(0, 1).ClearContents End If Next rng Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
Click File > Save As, choose Microsoft Excel Macro-Enabled Workbook (*.xlsm), and save the file. Reopen the file and click Enable Content if the macro security prompt appears.
Type OK in a cell in column D and confirm that column E shows the current date formatted as Paid on dd-mm-yyyy. Change the column D entry to anything else and confirm the date clears.
Automatically Record Paid Dates in WPS Spreadsheets with a Change Macro
WPS Spreadsheets can run the same VBA change-event macro, so you can stamp the current date into column E whenever column D changes to OK. Set a custom number format, paste the macro into the worksheet module, and save as an XLSM workbook to keep the automation.
- 1. Download and install WPS Office: Go to wps.com, download WPS Office, install it, and open your macro-enabled workbook in WPS Spreadsheets.
- 2. Open the VBA editor: Right-click the target worksheet tab and choose View Code to open the worksheet code module.
- 3. Paste the Worksheet_Change macro: Add the VBA code that watches column D, writes Date to column E when OK is entered, disables EnableEvents during the update, and clears the date otherwise.
- 4. Format column E: Select column E, open Format Cells > Custom, and enter "Paid on "dd-mm-yyyy so the date displays as Paid on 10-10-2023.
- 5. Save and test: Save as Microsoft Excel Macro-Enabled Workbook (*.xlsm), enable macros, type OK in column D, and confirm column E stamps the current date.
Frequently Asked Questions
Why does the date not appear when I type OK in column D?
The most common causes are that the file is saved as .xlsx instead of .xlsm, macros are disabled, or the code is in a standard module instead of the worksheet code module. Check File > Save As and choose Microsoft Excel Macro-Enabled Workbook, enable content, and paste the code by right-clicking the sheet tab > View Code.
How do I display the result as 'Paid on 10-10-2023' instead of a plain date?
Select column E, right-click > Format Cells > Number > Custom, and enter "Paid on "dd-mm-yyyy. The date is stored as a real date, so the custom prefix is display text only.
What happens if I change OK to another value later?
The Worksheet_Change macro clears the adjacent date cell when the column D entry is no longer OK. This prevents the paid date from remaining after the status changes.
Can I limit the macro to a specific row range instead of the entire column D?
Yes. Replace Range("D2:D" & Rows.Count) with a fixed range such as Range("D2:D100") in the Intersect call.
Do I need to use WPS Office to run this macro, or can I stay in Excel?
WPS Spreadsheets supports VBA and can open, edit, and save .xlsm files. You can use the same macro in WPS Office or Microsoft Excel, but WPS Office is free and compatible with Excel macro-enabled workbooks.
