VBA & Macro Problems

How to Show Excel Form Control Buttons Beside the Active Cell

Maira MehtabMaira Mehtab Nov 10, 2026 869 views

Question details

The user wants to dynamically display two Form Control buttons (add and subtract) right next to the active cell, restricted to columns C and D.

How to Show Excel Form Control Buttons Beside the Active Cell using VBA
Product
Excel
Device & OS
not provided
Scenario
Entering or adjusting numerical data quickly within a specific column range without manually moving the mouse to a static button location.
Observed behavior
The buttons should automatically appear beside the selected cell in the target range, add/subtract a fixed value when clicked, and disappear when a cell outside the range is selected.
Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to allow VBA scripts to run.

Solution 1Recommended

Dynamically Create Form Control Buttons via VBA

Use the Worksheet_SelectionChange event to delete existing buttons and generate new Form Control buttons next to the active cell.

Instead of using ActiveX controls, simple Form Control buttons are sufficient and often more stable for this scenario.

The logic involves deleting existing buttons and generating new ones immediately to the right of the active cell whenever the selection changes within your designated columns (e.g., C and D). You will need a worksheet event and a standard module to process the calculations.

1
Add the Worksheet Selection Change Event

Press ALT + F11 to open the VBA Editor. In the Project Explorer on the left, double-click the specific worksheet where you want the buttons to appear (e.g., Sheet1). Insert a 'Private Sub Worksheet_SelectionChange' event that calls an 'AddButtons' macro, passing 'Target' as the argument.

2
Create the Dynamic Button Logic

Right-click the project tree, select Insert > Module to create a standard module. Write a subroutine called 'AddButtons' that first deletes any existing buttons using 'ws.Buttons.Delete'. Then, check if the active cell falls within your target range (e.g., C2:D1048576) using the Intersect function. If it does, use 'ws.Buttons.Add' calculating the Left position based on 'Target.Left + Target.Width'.

3
Assign Operations to the Buttons

In the same module, create an 'ApplyOperation' subroutine. Set the OnAction property of both dynamically generated buttons to this macro. Inside the macro, use 'Application.Caller' to determine whether the Plus or Minus button was clicked. Validate that the cell contains a number with 'IsNumeric()', then increment or decrement the active cell's value by your predefined amount.

Prevent Non-Numeric Overwrites: Always include error handling in your macro to check if the target cell contains text. If you don't validate the cell type, the mathematical operation will fail and cause a VBA runtime error.
Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet and Macro Compatibility

If you frequently work with advanced spreadsheet functions, macros, and form controls, consider WPS Office. It provides excellent compatibility with Microsoft Excel formats and offers a familiar, intuitive interface, making your transition smooth and cost-effective.

Free and lightweight office suiteHigh compatibility with Microsoft Excel formats (.xlsx, .xlsm)Familiar ribbon interface for seamless user migrationRobust support for advanced data analysis and form controls
QA img-9

Frequently Asked Questions

Why aren't my VBA buttons appearing when I click a cell?

Ensure macros are enabled in your Excel Trust Center settings. Additionally, verify that the event trigger code is placed inside the specific Worksheet object module (like Sheet1), not in a standard module. The active cell must also fall within your specified intersection range.

Can I change the amount the buttons add or subtract?

Yes. You can declare a constant at the top of your standard module (e.g., 'Private Const OPERATION_VALUE As Double = 10') and modify this numeric value to whatever increment you require.

Do I need ActiveX controls for this to work?

No. Standard Form Control buttons are sufficient and highly recommended for dynamic creation and deletion via VBA, as they are generally lighter and less prone to resizing bugs than ActiveX controls.

How do I stop the buttons from replacing text with numbers?

You can use the 'IsNumeric(ActiveCell.Value)' function in your VBA code to check the contents before applying the math operation. If the cell contains text, the code should bypass the addition or subtraction.