Forum Discussion

UserOfExcel's avatar
UserOfExcel
Copper Contributor
Dec 09, 2025

Learning Macro Control Buttons

My goal is to embed two control buttons that pop up when a certain range of cells is selected. I have two columns (C:D) that have numeric entries for quantities of items. I want to be able to select a cell in this range and have two buttons appear next to active cell, one to add by an input value and one to subtract by an input value.

I was able to create Form Control Buttons that can do the task but I can't figure out how to align them to the active cell and hide them if the active cell is outside of the desired range. Any suggestions? Do I need to use ActiveX buttons instead of Form Control?

excel
Macros and VBA

1 Reply

  • VBasic2008's avatar
    VBasic2008
    Copper Contributor
    Dec 12, 2025

    Add Buttons Next to the Active Cell

    On each selection, the code will automatically delete the two buttons (if they exist) and create new ones to the right of the active cell only when the cell is part of the target range (C2:D1048576). Clicking the buttons increments or decrements the value in the active cell by the 'Operation Value'.

    Sheet Module, e.g., Sheet1

    Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    AddButtons Target
End Sub

    Standard Module, e.g. Module1

    Option Explicit

Private Const TOP_TARGET_ROW As String = "C2:D2"
Private Const OPERATION_MACRO_NAME As String = "ApplyOperation"
Private Const OPERATION_VALUE As Double = 10
Private Const CELL_BUTTON_GAP As Double = 0.75
Private Const BUTTON_BUTTON_GAP As Double = 0.75

Private Const PLUS_NAME As String = "btnPlus"
Private Const PLUS_CAPTION As String = "+"
Private Const PLUS_WIDTH As Double = 15
Private Const PLUS_HEIGHT As Double = 15

Private Const MINUS_NAME As String = "btnMinus"
Private Const MINUS_CAPTION As String = "-"
Private Const MINUS_WIDTH As Double = 15
Private Const MINUS_HEIGHT As Double = 15
Private Const OK_TO_REPLACE_NON_NUMERIC_WITH_ZERO As Boolean = False

Private Sub DeleteButtons(ByVal ws As Worksheet)
    On Error Resume Next
        ws.Buttons(Array(PLUS_NAME, MINUS_NAME)).Delete
    On Error GoTo 0
End Sub

Sub AddButtons(ByVal Target As Range)
    
    Dim ws As Worksheet: Set ws = Target.Worksheet
    DeleteButtons ws
    
    Dim trg As Range
    
    With ws.Range(TOP_TARGET_ROW)
        Set trg = .Resize(ws.Rows.Count - .Row + 1)
    End With
    
    Dim tcell As Range: Set tcell = ActiveCell
    If Intersect(tcell, trg) Is Nothing Then Exit Sub
    
    Dim btn As Button, LeftPosition As Double
    
    With tcell
        
        LeftPosition = .Left + .Width + CELL_BUTTON_GAP
        Set btn = ws.Buttons.Add(LeftPosition, .Top, PLUS_WIDTH, PLUS_HEIGHT)
        With btn
            .Name = PLUS_NAME
            .Caption = PLUS_CAPTION
            .OnAction = OPERATION_MACRO_NAME
        End With
    
        LeftPosition = LeftPosition + btn.Width + BUTTON_BUTTON_GAP
        Set btn = ws.Buttons.Add(LeftPosition, .Top, MINUS_WIDTH, MINUS_HEIGHT)
        With btn
            .Name = MINUS_NAME
            .Caption = MINUS_CAPTION
            .OnAction = OPERATION_MACRO_NAME
        End With
    
    End With
    
End Sub

Sub ApplyOperation()
    
    Dim ButtonName As String, CellNumber As Double
    
    On Error Resume Next
        ButtonName = Application.Caller
    On Error GoTo 0
    If ButtonName = "" Then Exit Sub
    
    With ActiveCell
        If IsNumeric(.Value) Then
            CellNumber = .Value
        Else
            If Not OK_TO_REPLACE_NON_NUMERIC_WITH_ZERO Then
                DeleteButtons .Worksheet
                Exit Sub
            End If
        End If
        Select Case ButtonName
            Case PLUS_NAME
                .Value = CellNumber + OPERATION_VALUE
            Case MINUS_NAME
                .Value = CellNumber - OPERATION_VALUE
            Case Else
        End Select
    End With

End Sub

     

Resources