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
--
Gary''s Student - gsnu200820


"AndrewCerritos" wrote:

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

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)


On Wed, 17 Dec 2008 12:50:08 -0800, AndrewCerritos
<(E-Mail Removed)> wrote:

>Hi, could anybody give me some hint or example on
>how to create button on the fly on a worksheet?
>
>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

"Gary''s Student" wrote:

> 1. View > Toolbars > Forms
> 2. click on the Button button
> 3. draw the button on the sheet
> 4. assign a macro to the button
> --
> Gary''s Student - gsnu200820
>
>
> "AndrewCerritos" wrote:
>
> > Hi, could anybody give me some hint or example on
> > how to create button on the fly on a worksheet?
> >
> > AndrewCerritos

Chip:

Thanks a lot. Works exactly as I intended.

AC

"Chip Pearson" wrote:

> 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)
>
>
> On Wed, 17 Dec 2008 12:50:08 -0800, AndrewCerritos
> <(E-Mail Removed)> wrote:
>
> >Hi, could anybody give me some hint or example on
> >how to create button on the fly on a worksheet?
> >
> >AndrewCerritos
>