Calling subroutine defined in another workbook (autostart workbook)
Thanks Dave, much appreciated. Your input helped me solve the issue.
The correct specification of the sub-routine invocation is:
Application.Run "MyMacros.xls!TestMsg.TestMsg"
i.e. WorkbookName!ModuleName.SubroutineName
It appears that ModuleName needs to be specified when there is more
than one module defined.
In the future, what I will follow as best practice:
Hit Alt-F8 to open the Available Macros window
Single Click the desired macro
Cut and Paste the full macro name from the Macro name: text entry
field in the Available Macros window
Thanks,
Scott
On Nov 16, 11:00*pm, Dave Peterson wrote:
My next guess is that there is at least one spelling mistake -- either in the
open workbook's name -- or in the macro name.
On 11/15/2010 21:00, Scott Bass wrote:
TestMsg is stored in:
MyMacros (MyMacros.xls)
* * Modules
* * * *TestMsg
On Nov 16, 11:51 am, Dave *wrote:
My first suggestion is to make sure that TestMsg is in a General module -- not
under ThisWorkbook and not behind a worksheet.
On 11/15/2010 18:40, Scott Bass wrote:
Hi,
I have a subroutine defined in my auto start workbook, which is named
MyMacros.xls:
MyMacros.xls:
Option Explicit
Sub TestMsg()
* * * MsgBox "Test Message"
End Sub
I want to run this macro whenever the save event runs in another
workbook, say temp.xls:
Temp.xls:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
* * * Application.Run "MyMacros.xls!TestMsg"
End Sub
This doesn't work. *I get the error message:
The macro 'MyMacros.xls!TestMsg' cannot be found. *I'm positive
MyMacros.xls is open (even if the workbook is hidden). *Alt-F11 shows
MyMacros.xls is open and the TestMsg module is defined.
How can I call the TestMsg sub-routine whenever I click Save in
another workbook?
Thanks,
Scott
--
Dave Peterson
--
Dave Peterson
|