add macro to wksht

  • Thread starter Thread starter i-Zapp
  • Start date Start date
I

i-Zapp

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
 
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).CodeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
"Msgbox ""Hi there"" "
End With
End Sub
 
Back
Top