Calling subroutine defined in another workbook (autostart workbook)

  • Thread starter Thread starter Scott Bass
  • Start date Start date
S

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
 
My first suggestion is to make sure that TestMsg is in a General module -- not
under ThisWorkbook and not behind a worksheet.
 
My next guess is that there is at least one spelling mistake -- either in the
open workbook's name -- or in the macro name.
 
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
 
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!!
 
Back
Top