How to Add an Input Value to a Running Total and Clear the Cell in WPS Spreadsheets Using VBA
Question details
The user needs a VBA macro that automatically adds the value entered in D9 to an existing running total in C7 and then clears D9, while avoiding recursive events and coexisting with other Worksheet_Change code.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Maintaining a running total in a worksheet where each new value entered in D9 is accumulated into C7 and then removed from the input cell.
- Observed behavior
- The macro should update C7 and clear D9 automatically. If events are not disabled, clearing D9 can trigger the Worksheet_Change event again and cause recursion.
Open the target workbook in WPS Spreadsheets and press Alt+F11 to confirm that the VBA editor is available. Identify the worksheet module for the sheet containing D9 and C7.
Use a Worksheet_Change Event to Update C7 and Clear D9 Safely
Use this method when you want the running total to update automatically whenever D9 changes, with Application.EnableEvents preventing the macro from triggering itself.
The Worksheet_Change event fires after a cell is edited. The Intersect check limits the code to D9, and the EnableEvents flag prevents the ClearContents action from retriggering the event. If the sheet already has a Worksheet_Change procedure, place the D9 block inside that existing procedure instead of adding a second one.
In WPS Spreadsheets, press Alt+F11 to open the VBA editor.
In the Project Explorer pane, double-click the sheet object for the worksheet that contains D9 and C7.
Paste the following code into the existing Worksheet_Change procedure if present; otherwise paste it as a new procedure: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("D9")) Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Range("C7").Value = Range("C7").Value + Range("D9").Value Range("D9").ClearContents CleanUp: Application.EnableEvents = True End Sub
Press Alt+Q to close the VBA editor, enter a number in D9, and confirm that C7 increases and D9 clears.
Use Defined Names for Input and Total Cells to Make the Code Easier to Maintain
Use this method when users may insert rows or move cells; defined names reduce the chance that direct cell references such as D9 and C7 break after layout changes.
Automate Running Totals with WPS Spreadsheets VBA
Use the WPS Spreadsheets VBA editor to run a Worksheet_Change macro that adds each D9 entry to C7, clears the input, and disables events during the update to prevent recursion.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the workbook containing the running total sheet, and confirm that macros can run.
- 2. Open the VBA editor: Press Alt+F11 to open the VBA editor and locate the worksheet module in Project Explorer.
- 3. Insert the Worksheet_Change macro: Paste the provided macro into the worksheet module so D9 updates C7 and clears automatically.
- 4. Save as a macro-enabled workbook: Click File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and save the file.
- 5. Run the macro: Enter a value in D9 and confirm that the running total updates in C7 and D9 clears.
Frequently Asked Questions
Why does the macro run again when D9 is cleared?
Clearing D9 is also a worksheet change, so the Worksheet_Change event fires twice unless you set Application.EnableEvents = False before clearing and set it back to True in a cleanup step.
Can I use named ranges instead of D9 and C7?
Yes. Select D9 and C7, use the Name Box to name them Input and Total, then replace Range("D9") and Range("C7") with Range("Input") and Range("Total") in the macro.
How do I save the workbook with the VBA code in WPS Spreadsheets?
Click File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and save. Avoid saving as .xlsx if you need to keep the macros.
Will this macro interfere with other Worksheet_Change code?
You can place the D9 check at the top of the existing Worksheet_Change procedure. The On Error GoTo CleanUp block re-enables events so other change macros are not left disabled if an error occurs.
