Try something like

Sub AAA()
Dim WB As Workbook
Dim WS As Worksheet
Dim OLEObj As OLEObject

Dim CodeString As String

Set WB = Application.Workbooks.Add
CodeString = "MsgBox" & Chr(34) & "hello world" & Chr(34)

Set WS = WB.Worksheets(1)
Set OLEObj = WS.OLEObjects.Add("Forms.CommandButton.1")
OLEObj.Name = "MyButton"
' OR
Set OLEObj = WS.OLEObjects("MyExistingOLEButton")

With WB.VBProject.VBComponents(WS.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", "MyButton") + 1, CodeString
End With
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"mccar75287" wrote in message
...
Here's my problem.
I have an Excel XLA which is installed on multiple workstations.
The XLA contains VBA code to create a new workbook and import a report
text
file into it and format it nicely.

It then adds a custom commandbutton into the open worksheet on the new
workbook and creates a new sub procedure for the on click event of the
command button using VBA code shown here.

Sub ModifyCommandButton1()


Dim ModEvent As CodeModule 'Module to Modified
Dim LineNum As Long 'Line number in module
Dim SubName As String 'Event to change as text
Dim Proc As String 'Procedure string
Dim EndS As String 'End sub string
Dim Ap As String 'Apostrophe
Dim Tabs As String 'Tab
Dim LF As String 'Line feed or carriage return

Ap = Chr(34)
Tabs = Chr(9)
LF = Chr(13)
EndS = "End Sub"

'Your Event Procedure OR SubRoutine
SubName = "Private Sub CommandButton1_Click()" & LF

'Your Procedure
Proc = "Call FlexTools.modInsertIntoMatstats.ProcessReport" & LF

'Use activeWorkbook so that it can act on another open/Active workbook
Set ModEvent = ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule
With ModEvent
LineNum = .CountOfLines + 1
.InsertLines LineNum, SubName & Proc & EndS
End With
End Sub




My new workbook ends up with this code in the onclick botton code.
But when I click the button it cannot find my VBA procedure called
ProcessReport which exists in the VBA Addin.

Iv'e found many links on how to call a procedure from another workbook
including the microsoft solution
below but the problem I have is that the workbook is created on the fly so
I
cannot create a reference from my workbook to my XLA Addin.


XL2000: How to Use a Custom Function in Another Workbook
http://support.microsoft.com/kb/213645

Is is possoble to create this reference through VBA? If not does anyone
have
any idea on how my new workbook can use code in the Addin?

Regards,
Jerry