create button on the fly in worksheet

[AndrewCerritos]

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

AndrewCerritos

 

[Gary''s Student]

1. View > Toolbars > Forms
2. click on the Button button
3. draw the button on the sheet
4. assign a macro to the button

 

[Chip Pearson]

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)

 

[AndrewCerritos]

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

 

[AndrewCerritos]

Chip:

Thanks a lot. Works exactly as I intended.

AC