Calling subroutine defined in another workbook (autostart workbook)
 

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

Calling subroutine defined in another workbook (autostart workbook)
 

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

Calling subroutine defined in another workbook (autostart workbook)
 

TestMsg is stored in:

MyMacros (MyMacros.xls)
Modules
TestMsg

On Nov 16, 11:51*am, Dave Peterson 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

Calling subroutine defined in another workbook (autostart workbook)
 

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

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

Calling subroutine defined in another workbook (autostart workbook)
 

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!!

On 11/17/2010 05:21, Scott Bass wrote:
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 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


--
Dave Peterson