create button on the fly in worksheet

  • Thread starter Thread starter AndrewCerritos
  • Start date Start date
A

AndrewCerritos

Hi, could anybody give me some hint or example on
how to create button on the fly on a worksheet?

AndrewCerritos
 
1. View > Toolbars > Forms
2. click on the Button button
3. draw the button on the sheet
4. assign a macro to the button
 
Try some code like the following:

Sub CreateButton()
' create the button
Dim Btn As Excel.Button
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
Set Btn = WS.Buttons.Add(Top:=50, Left:=100, Width:=40,
Height:=20)
With Btn
.Caption = "Click Me"
.OnAction = "'" & ThisWorkbook.Name & "'!TheProc"
End With
End Sub

Sub TheProc()
' called with button is clicked.
MsgBox "Hello World"
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Thanks. But I like to create the button on the fly from VBA only.
For example, when the book is open, it will invoke a routine to
loop through all the worksheets's name start with "ABC" and
place a button on it, including assign a macro to it.

AC
 
Back
Top