Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Calling A subroutine from a User Defined Function (UDF) that | Excel Programming | |||
Exceeding 65K of defined names within workbook causes workbook to go into repair mode when it is opened | Excel Programming | |||
HELP! Calling subroutine in different workbook | Excel Programming | |||
How to check workbook is already opened before passing the workbook obj to a subroutine in Word | Excel Programming | |||
Calling a Public Subroutine in a Different Workbook | Excel Programming |