How to Automatically Clear a Cell in WPS Office Spreadsheets When a Part Moves
Question details
The user wants to clear a shelf or location value automatically when a part is assigned to a technician.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user tracks parts in a spreadsheet and needs the shelf or location cell to become empty once a technician name is entered in another column.
- Observed behavior
- A formula in the same cell cannot clear its own original value without creating a circular reference, so the original shelf value must be stored separately or cleared with VBA.
If you plan to use the VBA method, save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and make sure the Developer tab is available in WPS Spreadsheets.
Clear Column B Automatically with a Worksheet Change Macro in WPS Spreadsheets
Use this solution when you need the original shelf or location cell to be cleared immediately after a technician name is entered in column A.
A formula cannot clear the original value in its own cell because that creates a circular reference. This worksheet-change macro bypasses that limitation by clearing the adjacent shelf/location cell after a change in column A.
In WPS Spreadsheets, click the Developer tab and then click Macros, or press Alt+F11 to open the VBA editor.
In the VBA editor Project pane, double-click the worksheet that contains column A and column B so its code window opens.
Paste the following code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then If Target.Value = "TECHS NAME" Then Target.Offset(0, 1).ClearContents End If End If End Sub
Go to File > Save As, choose Excel Macro-Enabled Workbook (*.xlsm), and click Save.
Type the technician name in column A and verify that the adjacent column B cell is cleared automatically.
Use a Helper Column Formula to Display a Blank Without Clearing the Original Shelf Cell
Use this solution when you want a formula-based, non-macro approach and can keep the original shelf value stored in column B.
Automate Cell Clearing in WPS Spreadsheets
You can complete this automation directly in WPS Spreadsheets by using a worksheet-change macro or a helper formula, without leaving the familiar Excel-compatible workflow.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select your Excel or macro-enabled workbook.
- 2. Enable the Developer tab if needed: Click the Menu button, go to Options, show the Developer tab in the ribbon, and return to the spreadsheet.
- 3. Create the worksheet-change macro: On the Developer tab, click Macros or press Alt+F11, paste the worksheet-change code into the target sheet module, and save as .xlsm.
- 4. Test the automation: Type the technician name in column A and confirm the adjacent shelf cell clears automatically.
Frequently Asked Questions
Can a formula clear the original shelf cell by itself?
No. A formula cannot change the value of its own cell without creating a circular reference. Store the source value in a separate column and use a helper formula, or clear column B with a VBA worksheet-change macro.
Why is my VBA macro not running in WPS Spreadsheets?
Make sure the workbook is saved as an Excel Macro-Enabled Workbook (.xlsm), macros are enabled in WPS Office security settings, and the worksheet-change code is pasted into the target worksheet module rather than a standard module.
Can I clear the cell based on any technician name instead of one specific name?
Yes. Change the macro condition to check for any non-empty value in column A, for example: If Target.Value <> "" Then Target.Offset(0, 1).ClearContents.
Does this work for an entire column?
Yes. The worksheet-change macro monitors column A with Intersect and clears the adjacent cell in column B for any monitored row. The helper formula can also be dragged down through the needed rows.
How do I preserve the original shelf value after it is cleared?
Store the original value in another column or a hidden sheet before clearing it, or use a helper display column instead of clearing the source cell.
