How to Stop the Excel Macro Dialog from Appearing Unexpectedly in WPS Office
Question details
The user needs to stop the Excel Macro dialog from appearing unexpectedly when running VBA code and place automatic cell-change code in the worksheet's Worksheet_Change event procedure.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Running VBA code from the Visual Basic Editor and creating a cell-change macro that should run automatically.
- Observed behavior
- The Macro dialog opens unexpectedly instead of running the intended code, usually because the code is in a standard module and cannot fire from a worksheet event.
Open the workbook in WPS Office Spreadsheets and press Alt+F11 to display the Visual Basic Editor. In the Project Explorer, confirm that you can see both standard modules and worksheet objects before moving any code.
Place Automatic Cell-Change Code in the Worksheet_Change Event
Use this solution when a macro should run automatically after a cell value changes, because code stored in a standard module will not fire from a worksheet event and may open the Macro dialog.
Worksheet_Change is an event procedure that belongs in the worksheet module, not in a standard module. Placing automatic cell-change code there lets WPS Office Spreadsheets run it directly when the target cell is edited.
In WPS Office Spreadsheets, open the workbook and press Alt+F11 to open the Visual Basic Editor.
In the Project Explorer pane, double-click the worksheet that contains the cells you want to monitor, such as Sheet1 (Sheet1).
In the code window, select Worksheet from the Object drop-down list and Change from the Procedure drop-down list to create the Private Sub Worksheet_Change(ByVal Target As Range) stub.
Type or paste your cell-change code between Private Sub Worksheet_Change(ByVal Target As Range) and End Sub, for example checking Target.Address or Target.Value.
Press Ctrl+S in the editor, close the editor, and then edit a cell in WPS Spreadsheets to confirm the macro runs automatically instead of using Run.
Run a Standard Sub Procedure Correctly from the Visual Basic Editor
Use this solution when you click Run or Play in the Visual Basic Editor and want to execute a standard macro without the Macro dialog appearing.
Use WPS Office Spreadsheets to Manage VBA Worksheet Change Macros Correctly
WPS Office Spreadsheets supports the VBA Worksheet_Change event, allowing you to store automatic cell-change code in the correct worksheet module and run standard macros from the Visual Basic Editor without unexpected Macro dialog interruptions.
- 1. Open the macro-enabled workbook: In WPS Office, click Menu > Open and choose the .xlsm or .xlsx workbook that contains your VBA code.
- 2. Open the Visual Basic Editor: Press Alt+F11 to open the WPS Visual Basic Editor and view the Project Explorer tree.
- 3. Select the correct worksheet module: Double-click the worksheet object where the cell-change macro should run, such as Sheet1 (Sheet1).
- 4. Create the Worksheet_Change event: In the code window, select Worksheet from the Object drop-down list and Change from the Procedure drop-down list, then paste or type your code inside the generated procedure.
- 5. Run and test your macros: For standard macros, place the cursor inside Sub MacroName() ... End Sub, click Run > Run Sub/UserForm, or press F5; then close the editor and edit a cell to confirm the automatic macro runs.
Frequently Asked Questions
Why does the Macro dialog appear when I click Run in the Visual Basic Editor?
The cursor is likely outside a Sub procedure. Click inside a macro block that begins with Sub MacroName() and ends with End Sub, then click Run > Run Sub/UserForm or press F5.
Where should I put code that runs automatically when a cell changes?
Put the code in the worksheet's Worksheet_Change event, not in a standard module. Press Alt+F11 to open the editor, double-click the worksheet in Project Explorer, and select Worksheet and Change from the drop-down lists.
Can I run a Worksheet_Change macro manually by pressing F5?
No, a Worksheet_Change event is triggered by editing a cell on the worksheet. Running it manually with F5 may open the Macro dialog because there is no standard Sub procedure selected.
Does moving code into Worksheet_Change work for .xlsm files in WPS Office?
Yes, WPS Office Spreadsheets supports VBA and worksheet events. Open the .xlsm workbook, move the automatic cell-change code into the worksheet module, and save it as a macro-enabled workbook.
