Learning Macro Control Buttons

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