add macro to wksht
 

Is there a programmatic way in VBA to add a simple macro (sub) to
workbook? i.e, a macro that inserts a macro.

The newly created sub can be contained in ThisWorkbook, the sheets, o
in a new Module.

My intention is that the macro would create a simple macro (for
button) that then resides within that workbook so as to allow macr
"portability", and is not limited to working only if the personal.xl
happens to have that macro

--
Message posted from http://www.ExcelForum.com

add macro to wksht
 

See Chip Pearson's
http://www.cpearson.com/excel/vbe.htm

Try this macro to create a button on Sheet1 and add code in the click event
in the sheet module.

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, _
"Msgbox ""Hi there"" "
End With
End Sub


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



"i-Zapp " wrote in message ...
Is there a programmatic way in VBA to add a simple macro (sub) to a
workbook? i.e, a macro that inserts a macro.

The newly created sub can be contained in ThisWorkbook, the sheets, or
in a new Module.

My intention is that the macro would create a simple macro (for a
button) that then resides within that workbook so as to allow macro
"portability", and is not limited to working only if the personal.xls
happens to have that macro.


---
Message posted from http://www.ExcelForum.com/