How to Run Excel VBA Code from a Save Button in WPS Spreadsheets
Question details
The user wants to run Excel VBA code when a Save button is clicked, including saving the workbook and adding formulas or other actions.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook has a Save button and the user wants one click to execute formulas or actions and then save the file.
- Observed behavior
- The Save button should call a VBA event that performs additional formulas or actions and then saves the workbook.
Make sure the Developer tab is visible in WPS Spreadsheets and that the workbook is saved as an Excel Macro-Enabled Workbook (*.xlsm) so the button macro can be stored and run.
Assign the Save Button to a VBA Macro in WPS Spreadsheets
Use this method when you need a button click to run formulas or other VBA statements and then save the workbook to a specific location.
The same VBA editor and button assignment workflow works for Form Controls and Shapes in WPS Spreadsheets. Any workbook that contains the Save button macro should be saved in a macro-enabled format.
In WPS Spreadsheets, click the Developer tab, then click Visual Basic, or press Alt+F11 to open the VBA editor.
In the Project Explorer, double-click the worksheet module that contains the Save button, or go to Insert > Module to add a standard module.
Paste a subroutine such as Private Sub btnSave_Click() and add the formulas or actions before saving, for example Range("A1").Formula = "=SUM(B1:B10)" followed by ThisWorkbook.SaveAs "C:\Path\To\File.xlsx".
Return to the worksheet, right-click the Save button, choose Assign Macro, select btnSave_Click or the created macro, and click OK.
Save the workbook as Excel Macro-Enabled Workbook (*.xlsm), then click the Save button to run the code and verify the save location and formulas.
Run Save-Button VBA Code in WPS Spreadsheets
WPS Spreadsheets includes a VBA editor and macro-enabled workbook support, so you can write a Save button routine, apply formulas, and save the workbook just like in Excel.
- 1. Enable the Developer tab: Open WPS Spreadsheets and ensure the Developer tab is visible; if not, right-click the ribbon, choose Customize Ribbon, and check Developer.
- 2. Open Visual Basic: Click Developer > Visual Basic or press Alt+F11 to open the VBA editor.
- 3. Create the Save button routine: Add a module or open the worksheet module, then write a subroutine such as btnSave_Click. Place formulas like Range("A1").Formula = "=SUM(B1:B10)" before ThisWorkbook.SaveAs to save the workbook.
- 4. Assign and test the macro: Right-click the Save button on the sheet, choose Assign Macro, select btnSave_Click, save the file as .xlsm, and click the button to test.
Frequently Asked Questions
Does WPS Spreadsheets support Excel VBA macros?
Yes, WPS Spreadsheets supports VBA macros and can open and save Excel macro-enabled workbooks (.xlsm) on compatible platforms. Use Developer > Visual Basic to write or edit macros.
Why doesn't the SaveAs code work when I click the button?
Check that the file is saved as .xlsm, the macro is assigned to the button, macro security is not blocking it, and the SaveAs path is valid and writable.
How do I add a formula before the save action in the VBA code?
Place the formula statement before ThisWorkbook.SaveAs in the same subroutine, for example Range("A1").Formula = "=SUM(B1:B10)". The formula will execute when the button is clicked.
Can I assign a macro to a shape or Form Control button in WPS Spreadsheets?
Yes. Right-click the shape or button, choose Assign Macro, select the subroutine, and click OK.
Why does my macro disappear after saving?
If you save as .xlsx, macros are removed. Save as Excel Macro-Enabled Workbook (*.xlsm) or a WPS macro-enabled format so the VBA code is retained.
