How to Add VBA Buttons to Add and Delete Repeating Form Sections in Excel
Question details
The user wants to create VBA Add and Delete buttons in an Excel form that duplicate or remove repeating sections while preserving formatting, resetting drop-down values, and clearing input cells.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a multi-section Excel form where users can dynamically duplicate or remove repeated sections by clicking Add and Delete buttons.
- Observed behavior
- The repeating sections must keep their formatting, reset drop-down choices, and clear additional input fields when added or deleted, while the first entry remains protected from deletion.
Save the workbook as a macro-enabled file (.xlsm), enable macros, and open the Visual Basic Editor with Alt+F11 so you can insert and assign the VBA code for the Add and Delete buttons.
Use VBA Macros to Add and Delete Repeating Form Sections in Excel
Use this solution when you need Add and Delete buttons that duplicate the last populated section, reset drop-down values, and clear extra input cells without removing the first entry.
This VBA approach locates the last populated section, copies and inserts the required rows, resets option or drop-down cells, and clears additional details. The Delete procedure removes only the last section and includes a guard condition to prevent deleting the first entry.
Press Alt+F11 in WPS Spreadsheets or Excel, right-click the workbook project in the Project Explorer, and choose Insert > Module to create a code module.
Insert VBA procedures that find the last row of the repeating section, copy the required row range, insert the copied rows below it, reset drop-down or option cells to their default values, and clear additional input cells.
Right-click the Add button on the form, select Assign Macro, choose the AddSection macro, and click OK. Repeat the same steps for the Delete button and choose the DeleteSection macro.
Exit Design Mode, click Add and Delete to confirm sections are duplicated or removed correctly and the first section cannot be deleted, then save the file as an Excel Macro-Enabled Workbook (.xlsm).
Protect the First Section and Validate Deletion Logic
Use this solution to prevent accidental deletion of the first entry and to lock the template section so the repeated form structure stays intact.
Create Add and Delete VBA Buttons for Repeating Form Sections in WPS Spreadsheets
WPS Spreadsheets supports macro-enabled workbooks, so you can insert VBA code for Add and Delete buttons, preserve section formatting, reset drop-down values, and clear inputs while keeping an Excel-compatible workflow.
- 1. Enable the Developer tab: In WPS Spreadsheets, go to File > Options > Customize Ribbon and check Developer to access macro and form controls.
- 2. Open the VBA editor and insert a module: Press Alt+F11, right-click the VBA project, and choose Insert > Module to paste the Add and Delete section code.
- 3. Assign macros to form buttons: Insert two form control buttons via Developer > Insert > Button, then right-click each button and choose Assign Macro to link AddSection and DeleteSection.
- 4. Save as a macro-enabled workbook: Go to File > Save As, choose Excel Macro-Enabled Workbook (.xlsm), and keep macros enabled when the security prompt appears.
Frequently Asked Questions
Does WPS Spreadsheets support VBA macros for Excel forms?
Yes, WPS Spreadsheets can open and run macro-enabled .xlsm files. Enable macros at the security prompt and use Alt+F11 to open the VBA editor for Add and Delete button code.
How do I stop the Delete button from removing the first form section?
Add a guard condition in the Delete macro that exits the subroutine when the last populated section row equals the first section row. You can also lock the first section cells and use Review > Protect Sheet.
How do I assign a macro to an Add or Delete button?
Right-click the button, select Assign Macro, choose the relevant macro name, and click OK. In WPS Spreadsheets, the same command is available from the Developer tab after you insert a form control button.
Why should I save the workbook as .xlsm?
The .xlsm format stores VBA macros. If you save the workbook as .xlsx, the macro code is removed and the Add/Delete buttons will no longer work.
How do I reset drop-down values and clear inputs after adding a section?
In the Add macro, after inserting the copied rows, set the new drop-down cells to the default list value and clear additional input cells by assigning an empty value or the appropriate default to the target Range.
