VBA & Macro Problems

How to Use VBA to Add a Formatted Row with a Button in Excel

Huma Ashraf Ch Oct 9, 2026 868 views

Question details

The user needs to create an Excel VBA macro attached to a button that asks for confirmation, inserts a new row below the active one, copies formatting and validation, preserves formulas, and clears constants.

How to Use VBA to Add a Formatted Row With a Button in Excel

Product: Excel
Device & OS: not provided
Scenario: Automating data entry by clicking a button to insert a new, clean, formatted row while maintaining underlying formulas and data validation rules.
Observed behavior: The user wants a safe way to achieve this workflow, avoiding previous macro errors that accidentally cleared all worksheet text or caused issues when using ActiveX controls.

Before you start

Always save a backup copy of your workbook before running or testing new VBA macros, as VBA actions usually clear the Undo history and cannot be easily reversed.

Solution 1Recommended

Use a Worksheet Shape with a VBA Macro (Recommended)

Using a standard worksheet shape instead of an ActiveX control provides better stability. This macro will copy the active row, insert a new row below it, and clear only the typed constants to preserve your formulas.

ActiveX buttons can sometimes cause resizing bugs or trigger unintended worksheet text clearance if not configured perfectly. Using a simple drawn shape assigned to a targeted VBA script is a much safer alternative.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor. Go to Insert > Module to create a new blank module.

Enter the Macro Code

Paste a VBA script that identifies the active row, copies it, and inserts it below. Ensure the script includes a line like 'Selection.SpecialCells(xlCellTypeConstants).ClearContents' to only clear text/numbers but keep formulas.

Insert a Form Button or Shape

Return to your Excel worksheet. Go to the Insert tab, select Illustrations > Shapes, and draw a rectangle (or use Developer > Insert > Form Controls > Button) at the top of your sheet.

Assign the Macro

Right-click the shape or button you just created, click 'Assign Macro...', select the macro you pasted in step 2, and click OK.

Test the Button

Click on any filled row in your dataset to make it the active row, then click your new button. Confirm that a new row appears below, empty of typed data but retaining the formulas and formatting.

Use a Worksheet Shape with a VBA Macro (Recommended)

Shape vs ActiveX: Using a worksheet shape instead of an ActiveX drop-down or button prevents common compatibility issues and makes the macro much easier to maintain for regular users.

Automate Tasks with WPS Office

Easily Manage Macros and Data in WPS Spreadsheets

WPS Office provides robust built-in Developer tools, allowing you to write, edit, and run VBA macros seamlessly to automate tasks like inserting formatted rows.

1. Open your macro-enabled file: Launch WPS Spreadsheets and open your existing .xlsm workbook.
2. Enable the Developer Tab: If not visible, go to Settings or Options to enable the Developer tab in your ribbon.
3. Access the VBA Editor: Click 'Macros' or 'Visual Basic' in the Developer tab to open the editor and paste your row-insertion code.
4. Assign and Run: Insert a shape from the Insert tab, right-click it to assign your macro, and click to automatically insert your formatted rows.

Highly compatible with Microsoft Excel (.xlsx and .xlsm) macro-enabled formats.Built-in Developer tab for easy access to VBA modules and script execution.Familiar user interface makes assigning macros to shapes and buttons intuitive.Lightweight, fast, and free to use for daily spreadsheet automation tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro clear all formulas instead of just the text?

This happens if the VBA code uses a general 'ClearContents' command on the entire row. To fix this, your code must specifically target constant values by using '.SpecialCells(xlCellTypeConstants).ClearContents', which leaves your formulas and data validation intact.

What is the difference between an ActiveX button and a Form Control shape?

Form Control shapes are simpler, more stable, and less prone to resizing bugs across different screen resolutions or Office updates. ActiveX controls offer more advanced properties and events but can be overly complex and sometimes cause worksheet glitches.

Can I undo a VBA macro action in Excel?

By default, running a VBA macro clears the Excel Undo history. You cannot use Ctrl+Z to reverse the actions performed by the script, which is why you should always test new macros on a backup copy of your workbook.

Excel & Data Problems