Add Button To Fit Selection Excel VBA Macro

This macro adds a control button that fits into the selected range in the active sheet. The code can easily be leveraged to add several buttons using loops, for example, if we want to add buttons to each cell within a column.

Macro/VBA code:

  Sub AddButtonToFitSelection()

      Dim tRng As Range, btn As Object

      Dim x As Integer, y As Integer, w As Integer, h As Integer

      Set tRng = Selection

      x = tRng.Left

      y = tRng.Top

      w = tRng.Width

      h = tRng.Height

      Set btn = ActiveSheet.Buttons.Add(x, y, w, h)

      btn.OnAction = "ButtonAction"

  End Sub

  Sub ButtonAction()

      MsgBox "You have pressed the button"

  End Sub

Macro explained:

• First we declare a range object variable (tRng) that will hold the range selection and an object variable (btn) that will be the button, along with the coordinates to position the button (x,y,w,h) as integers.
• Then we set the tRng range variable to whatever is selected on the active sheet.
• We get the range dimension coordinates with the Left, Top, Width, and Height properties of the Range object.
• Then we add a control button with those same coordinates so that it fits into the selection. Note that we set the button to the object variable btn.
• Next we set the event OnAction to run a specified procedure (ButtonAction) when the button is clicked. Similarly, we could apply other events, methods or properties. For example, we could show some text on the button with the Caption property as follows: btn.Caption = “Run the macro”
• The ButtonAction procedure is triggered when the button is clicked and it just displays a box with that message.

This is how we add buttons to fit the selection in Excel with VBA macros.

Other examples:

• How to List and Link All Sheets with Excel VBA macros
• How to List Unique Entries with Excel VBA macros
• How to Sort Sheets Alphabetically with Excel VBA macros