How to Show Excel Form Control Buttons Beside the Active Cell
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.

- 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.
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.
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.
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.
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'.
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.
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.

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.




