Does anyone know how to add a button to a spreadsheet to run an existing
MACRO?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Try this
It will run your sub mymacro

Sub test()
Dim WS As Worksheet
Dim Btn As OLEObject
Set WS = ThisWorkbook.Worksheets("Sheet1")
With WS
Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1" , _
Left:=.Range("C3").Left, Top:=.Range("C3").Top, _
Width:=100, Height:=30)
End With
Btn.Object.Caption = "Click Me"
Btn.Name = "TheButton"
With ThisWorkbook.VBProject.VBComponents(WS.CodeName).C odeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
"mymacro"
End With
End Sub

Sub mymacro()
MsgBox "Hi"
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"STEVEB" wrote in message ...
Does anyone know how to add a button to a spreadsheet to run an existing
MACRO?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

with code or manually?

make the forms toolbar visible. Click on the button. Use the mouse to draw
a rectangle on the worksheet where you want it.

You should be prompted to assign a macro, select the existing macro.

--
Regards,
Tom Ogilvy


"STEVEB" wrote in message
...
Does anyone know how to add a button to a spreadsheet to run an existing
MACRO?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Show the "Forms" toolbar. Choose the "Button" control and
draw it on your spreadsheet. Wizard will ask you what
macro to associate it with

Gavin


-----Original Message-----
Does anyone know how to add a button to a spreadsheet to
run an existing
MACRO?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.