How to Fix a VBA Worksheet Change Event to Update Only the Changed Table Row in WPS Spreadsheets
Question details
The user needs a VBA Worksheet_Change event that updates only the specific table row affected by a change instead of checking an entire column or only one cell.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet change event in a table should update only the matching New cell when any Current or Replaced cell changes, including multi-cell edits such as dragging a value down a column.
- Observed behavior
- Checking an entire column or using only the first cell in Target can overwrite every New cell in the table and can cause Type Mismatch errors.
Open the workbook in WPS Spreadsheets and confirm that the sheet has a ListObject table with Current, Replaced, and New columns. Press Alt+F11 to open the VBA editor, and copy the existing Worksheet_Change code to a text file before replacing it.
Use Intersect and a row-by-row loop in Worksheet_Change
Use this when you need the change event to process only the changed row or rows, including multi-cell drag-and-fill edits in the Current or Replaced columns.
The Worksheet_Change event receives the Target range, but Target may contain multiple cells. Checking an entire column recalculates every New cell, while reading only Target.Cells(1) ignores the rest. Intersect against the table's DataBodyRange limits processing to changed Current and Replaced cells, and a For Each loop updates only the New cell in each affected row.
In WPS Spreadsheets, press Alt+F11 to open the VBA editor, expand Microsoft Excel Objects in the Project Explorer, and double-click the sheet module that contains the table.
At the top of the Worksheet_Change procedure, add Application.EnableEvents = False and an On Error GoTo label. In the exit and error handler, restore Application.EnableEvents = True before the procedure ends.
Set rngChgdCurrentCells = Intersect(Target, tbl.ListColumns("Current").DataBodyRange) and rngChgdReplacedCells = Intersect(Target, tbl.ListColumns("Replaced").DataBodyRange). If both are Nothing, exit without processing.
Create a union of the non-empty changed ranges, then use For Each rngCell in rngChgdRelevantCells to get intRow = rngCell.Row. This processes every changed row instead of only the first cell in Target.
Inside the loop, set C, R, and N to that row's Current, Replaced, and New table cells. If R is empty do nothing; if R equals "No" set N.Value = C.Value; otherwise prompt for the site address and assign it to N.Value.
Use WPS Spreadsheets VBA to Update Only the Changed Table Row
Write a Worksheet_Change event in WPS Spreadsheets that uses Intersect and a row-by-row loop so Current/Replaced edits update only the matching New cell without overwriting the entire column.
- 1. Open WPS Spreadsheets and enable macros: Open your workbook in WPS Spreadsheets. If the Developer tab is not visible, right-click the ribbon, choose Customize Ribbon, and enable the Developer tab.
- 2. Open the VBA editor and select the worksheet module: Press Alt+F11 to open the VBA editor. In the Project Explorer, double-click the target sheet under Microsoft Excel Objects, then paste or edit the Worksheet_Change code.
- 3. Match the table and column names: Confirm that the table index and column names Current, Replaced, and New in the code exactly match the ListObject and ListColumns in your sheet.
- 4. Test a row update in Current or Replaced: Return to the worksheet, change one Replaced cell to No or another value, and verify that only the New cell in that row updates. Test a multi-cell drag to confirm every affected row updates.
- 5. Save as a macro-enabled workbook: Go to File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm) or the WPS-compatible macro format so the VBA code is retained.
Frequently Asked Questions
Why does my Worksheet_Change event overwrite every New cell instead of only the edited row?
The code is likely checking an entire column or table column, so every New cell recalculates on any change. Use Intersect(Target, specific column DataBodyRange) to restrict processing to the changed cells only.
How do I handle multiple cells changed at once, such as dragging down the Replaced column?
Do not rely on Target.Cells(1) or Target.Value. Loop through each cell returned by Intersect(Target, Current/Replaced DataBodyRange) so every changed row is processed.
Why do I get a Type Mismatch error when comparing a whole row or column?
A multi-cell Range cannot be treated as a single value. Use Intersect with the individual row cells and read only the specific C, R, and N cells for that row, as shown in the recommended solution.
Do I need to disable events inside a Worksheet_Change event?
Yes. Add Application.EnableEvents = False after entering the handler and restore it to True in both normal exit and error handling, otherwise the event can fire repeatedly.
How can I make the "No" comparison case-insensitive in VBA?
Replace R.Value = "No" with StrComp(R.Value, "No", vbTextCompare) = 0 so that "no", "NO", and "No" are treated the same.
