M
mccar75287
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").CodeModule
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
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").CodeModule
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