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.



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

TestMsg is stored in:

MyMacros (MyMacros.xls)
Modules
TestMsg

On Nov 16, 11:51*am, Dave Peterson <peter...@XSPAMverizon.net> 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

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 Peterson<peter...@XSPAMverizon.net> 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

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 <peter...@XSPAMverizon.net> wrote:
> My next guess is that there is at least one spelling mistake -- either inthe
> 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 Peterson<peter...@XSPAMverizon.net> *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

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 Peterson<peter...@XSPAMverizon.net> 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 Peterson<peter...@XSPAMverizon.net> 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