Introduction

There are three types of Excel Macro buttons which I’ve described in the previous post on how to create them manually in Excel, along with their advantages and disadvantages. In this post, I’m going to explain to you how to create them using VBA. Creating buttons with VBA will save you more time if you create many sheets with buttons. Also, I will explain the common styling functions which you can use to make the buttons look good in your project.
The button types are:-

Form Button
ActiveX Button
Shape Button

Form Button

Form button is the most basic button type, and this looks almost like Windows 95 button. So, there are not many display options that you can use to stylize the button.

Sub CreateFormButton()
    Dim btnRange As Range
    Dim bt As Button

    Set btnRange = Range("A1")
    Set bt = ActiveSheet.Buttons.Add(btnRange.Left, btnRange.Top, btnRange.Width, btnRange.Height) ' Making the button cover the full cell

    bt.Name = "FormButton"                 ' Name of the button
    bt.OnAction = "TestMacro1"             ' OnClick TestMacro1 Subroutine called
    bt.Caption = "Form Button"             ' Label of the button
    bt.Font.Name = "Arial"                 ' Setting Font for Label
    bt.Font.Color = RGB(0, 0, 0)           ' Assign Font Color of Label
    bt.Font.Bold = True                    ' Setting Bold for Label
    bt.Font.Size = 16                      ' Setting Size of Label
    bt.Placement = xlMoveAndSize           ' Move And Size with Cell
End Sub

This code will create a button over Cell A1 as we use its dimensions to create the button. If you want to cover more cells like A1 to C2, you need to change it to A1:C2. For the form button, we are only able to change the font color. We cannot change the button background, etc. If we want to move the button when we change the dimensions of rows & columns, then we’ve to use xlMoveAndSize for the placement property.

Also, for OnAction, we have defined TestMacro1, which you can change to your respective function which you want to use.

Placement Options for Buttons

Placement Property	Description
xlFreeFloating	The button created will stay in the same location, even if you resize your rows and columns.
xlMove	The button will move when you resize your rows and columns.
xlMoveAndSize	The button will change its dimensions accordingly, so if you’ve covered the cell, it will stay covered even if you change row height or column width.

ActiveX Button

This button type has more properties that you can configure. For this, we can also change the background color of the button. We have to implement the on-click macro function separately, and it doesn’t have the OnAction property.

Sub CreateActiveXButton()
    Dim bt As Object
    Dim Rng As Range
    Set Rng = Range("A1")
    Set bt = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height)
    bt.Name = "ActiveXButton"                     ' Name of button
    bt.Object.Caption = "ActiveX Button"          ' Label of button
    bt.Object.Font.Name = "Arial"                 ' Setting Font for Label
    bt.Object.Font.Bold = True                    ' Setting Bold for Label
    bt.Object.Font.Size = 16                      ' Setting Size of Label
    bt.Object.BackColor = RGB(255, 0, 0)          ' Setting Background color for button
    bt.Object.ForeColor = RGB(255, 255, 255)      ' Setting Label color for button
    bt.Placement = xlMoveAndSize                  ' Move And Size with Cell
End Sub
Private Sub ActiveXButton_Click()
End Sub

For the ActiveX button, we can change both the foreground and background color, making the button look more attractive.

Shape Button

I prefer the Shape button compared to Form and ActiveX as it has more customization options. If you are not into the look and feel, then the best option would be to go with the Form button, but if you want to give a better user experience, then the Shape button is vital.

Sub CreateShapeButton()
  Dim Shp1 As Shape
  Dim rng As Range
  Set rng = Range("A1")
  Set Shp1 = ActiveSheet.Shapes.AddShape(msoShapeRoundedRectangle, rng.Left + 5, rng.Top + 5, rng.Width - 10, rng.Height - 10)

  Shp1.Fill.Visible = msoTrue

  Shp1.TextFrame.Characters.Caption = "Shape Button"     ' Setting label of Shape button
  Shp1.TextFrame.HorizontalAlignment = xlHAlignCenter    ' Aligning the label text to be placed in Center Horizontally
  Shp1.TextFrame.VerticalAlignment = xlVAlignCenter      ' Aligning the label text to be placed in Center Vertically
  Shp1.TextFrame.Characters.Font.Size = 32               ' Font size of Label

  Shp1.TextFrame.Characters.Font.Bold = True             ' Setting all the characters as Bold

    With Shp1.TextFrame2.TextRange.Characters.Font.Fill  ' Some more advanced coloring of the Label
        .Visible = msoTrue                               ' Making it visible
        .ForeColor.RGB = RGB(251, 133, 0)                ' Giving an orangish color
        .Transparency = 0                                ' Making it opaque
        .OneColorGradient msoGradientFromCenter, 1, 0.7  ' Single color gradient
    End With
  Shp1.Fill.ForeColor.RGB = RGB(6, 107, 157)             ' Setting background color of button

  Shp1.OnAction = "TestMacro1"                           ' Assigning macro

  With Shp1.Line                                         ' Advanced border properties for Shape.
    .Visible = msoTrue
    .ForeColor.RGB = RGB(142, 202, 230)                  ' Light blue color
    .Transparency = 0
    .Weight = 3                                          ' Thicker border
  End With
End Sub

If you read the syntax above, you can see numerous options to configure for Shape. This allows us to make unique customizations to the display of the button. I’ve just scratched the surface of the options available for Shape, and you can do so much more.

One more twist of Shapes is that the placement option doesn’t work as required, but if you want to move the shape while your sorting, etc., the key is to have your shape very slightly inside the cell. Rather than putting it right at the top-left corner with the same height as the cell, place it a point below and a point to the right of the top-left corner with a height 2 points shorter than the cell.

Conclusion

In this post, we have learnt how to create all the different types of excel macro buttons using VBA directly. Making use of VBA in your project will help you immensely speed up your work in Excel. It’s just a matter of learning and using the properties.

If you have any queries related to this post or need any help feel free to comment or write to me at [email protected]