Calling subroutine defined in another workbook (autostart workbook)

[Scott Bass]

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]

My first suggestion is to make sure that TestMsg is in a General module -- not
under ThisWorkbook and not behind a worksheet.

 

[Scott Bass]

TestMsg is stored in:

MyMacros (MyMacros.xls)
Modules
TestMsg

 

[Dave Peterson]

My next guess is that there is at least one spelling mistake -- either in the
open workbook's name -- or in the macro name.

 

[Scott Bass]

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

 

[Dave Peterson]

It's not the number of modules that caused your problem. It's that you named it
the same as the macro name.

If you renamed the module (or the macro) to something like: Mod_TestMsg
then your original code would work fine.

And you may find that your UDFs called from a cell in a worksheet will work, too!!