- Rahul Neelakantan
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 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
The button created will stay in the same location, even if you resize your rows and columns.
|The button will move when you resize your rows and columns.|
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.
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.
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.
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]