Create Form Control Buttons Dynamically On A Worksheet With VBA
Occasionally developers need to dynamically create buttons on a worksheet that are linked to a macro. Excel has two type of controls: Forms and Active-X. The code below illustrates the Forms Control type of button. There are many reasons for this requirement, but one of the most common is that users create new worksheets in an application but don't want to call the developer back to insert buttons in the new worksheet. This method will create a forms button in any new worksheet dynamically, eliminating the need for the developer to have to constantly update the application.
Program Code
Option Explicit
Sub CreateFormsButton()
' ********************************************************
' This Creates A Form Control Button (Not Active-X)
' And Assigns It To An Existing Macro
' Buttons.Add (Left Position, Top Position, Width, Height)
' ********************************************************
' *******************************************************
' Remove All Pre-Existing Buttons From The Sheet
' *******************************************************
ActiveSheet.Buttons.Delete
' *******************************************************
' Create The New Button - The Next Line is Absolute:
' ActiveSheet.Buttons.Add(199.5, 20, 90, 25).Select
' *******************************************************
' *******************************************************
' The Production Version Is Range Relative
' *******************************************************
ActiveSheet.Buttons.Add(Range("E3").Left, Range("E3").Top, 90, 25).Select
Selection.Name = "btnCreateQuote"
Selection.OnAction = "MessageMe"
ActiveSheet.Shapes("btnCreateQuote").Select
With Selection
.Characters.Text = "Create Quote"
With .Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
End With
End With
ActiveSheet.Cells(1, 1).Select
End Sub
Public Sub MessageMe()
MsgBox ("Hello - I Have Been Activated By A Button")
End Sub
Public Sub DeleteButton()
Dim Shape As Shape
Dim i As Long
' *******************************************************
' --- Remove All Buttons From The Sheet ---
' This Example Shows Two Methods
' *******************************************************
' *******************************************************
' Method 1:
' ActiveSheet.Buttons.Delete
' *******************************************************
' *******************************************************
' Method 2:
' This Loops Through All Shapes And Only Delete
' Forms Control Buttons
' *******************************************************
For Each Shape In Sheets("Sheet1").Shapes
If Shape.Type = msoFormControl Then
If Shape.FormControlType = xlButtonControl Then
Shape.Delete
End If
End If
Next Shape
End Sub
' *****************************************************************************
' Create Buttons That Fit Exactly In A Cell And Assign To A Macro
' *****************************************************************************
Public Sub CreateFormsbuttons()
Dim wkbCreateQuoteWorkbook As Workbook
Dim wksQuote As Worksheet
Dim intColumnNumberOfQuote
Set wkbCreateQuoteWorkbook = ThisWorkbook
Set wksQuote = wkbCreateQuoteWorkbook.Sheets("Quote")
' ************************************************************
' Find Leftmost Column Of Quote Worksheet
' ************************************************************
intColumnNumberOfQuote = wksQuote.Cells(3, Columns.Count).End(xlToLeft).Column
' ***********************************************
' Delete Previous Buttons
' ***********************************************
wksQuote.Buttons.Delete
' ***********************************************
' Put Buttons In Worksheet That Fit The Cell
' Exactly And Place Them In Active Columns
' Starting At Column 2
' ***********************************************
For i = 2 To intColumnNumberOfQuote
wksQuote.Cells(3, i).Value = "Description Or Quantity"
Set rngButton = wksQuote.Cells(2, i)
Set btnButton = wksQuote.Buttons.Add(rngButton.Left, rngButton.Top, rngButton.Width, rngButton.Height)
With btnButton
.OnAction = "DeleteQuoteColumn"
.Caption = "Delete"
.Name = "Delete" & i
End With
Next i
wksQuote.Activate
End Sub
' ***********************************************
' Here Is A Subroutine That Determines In Which
' Column A Forms Button Was Clicked So It Can
' Delete That Column
' ***********************************************
Public Sub DeleteQuoteColumn()
Dim rngButtonClick As Range
Dim wkbCreateQuoteWorkbook As Workbook
Dim wksQuote As Worksheet
Set wkbCreateQuoteWorkbook = ThisWorkbook
Set wksQuote = wkbCreateQuoteWorkbook.Sheets("Quote")
Set rngButtonClick = ActiveSheet.Buttons(Application.Caller).TopLeftCell
wksQuote.Columns(rngButtonClick.Column).Delete
End Sub
' ****************************************************************************
Some Other Properties of the Forms Control Button When Application.Caller
Is Used:
' ****************************************************************************
? ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address
? ActiveSheet.Buttons(Application.Caller).Name
? ActiveSheet.Buttons(Application.Caller).Caption
? ActiveSheet.Buttons(Application.Caller).Width
? ActiveSheet.Buttons(Application.Caller).Height
? ActiveSheet.Buttons(Application.Caller).Font.Name
? ActiveSheet.Buttons(Application.Caller).Font.Size
? ActiveSheet.Buttons(Application.Caller).Font.Color
? ActiveSheet.Buttons(Application.Caller).Font.FontStyle
