| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a public Macro from a Private Macro
Yet another question!!!
I am trying to use the Worksheet_change Event to call a macro that I have placed in the "ThisWorkbook" folder using Application.run. The problem I am having is that I do not want to use the specific name of the workbook in the statement because it can change since I am making a "Template". Any suggestions how I can call the Active workbook instead?!?! I have tried several itterations and nothing seems to work. I keep getting application failure. I assume it cannot find the macro.... Example: Worksheet name - myworksheet macro name - runme Application.run "'myworksheet.xls'!ThisWorkbook.runme" but replace "myworksheet.xls" with a generic statement Thank you! -- I am not where I intended to go, but I think I am where I am supposed to be! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling a public Macro from a Private Macro
ActiveWorkbook is the active workbook. ThisWorkbook is the workbook that
holds the macro that is running. Does that help? Otto "thewizz" wrote in message ... Yet another question!!! I am trying to use the Worksheet_change Event to call a macro that I have placed in the "ThisWorkbook" folder using Application.run. The problem I am having is that I do not want to use the specific name of the workbook in the statement because it can change since I am making a "Template". Any suggestions how I can call the Active workbook instead?!?! I have tried several itterations and nothing seems to work. I keep getting application failure. I assume it cannot find the macro.... Example: Worksheet name - myworksheet macro name - runme Application.run "'myworksheet.xls'!ThisWorkbook.runme" but replace "myworksheet.xls" with a generic statement Thank you! -- I am not where I intended to go, but I think I am where I am supposed to be! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling a public Macro from a Private Macro
In order to call procedures in ThisWorkbook from outside of the ThisWorkbook
module you need to preface the call with ThisWorkbook. So for example in Module 1 to call a procedure in ThisWorkbook you need to use something like this... Call ThisWorkbook.RunMe I am unclear why you would be worried about which is the active workbook and using Application.Run? Are you trying to run a procedure located in one workbook on a different workbook? So are you wanting to run Book1.ThisWorkbook.RunMe on as sheet in book2? If so then that is a different beast all together. -- HTH... Jim Thomlinson "thewizz" wrote: Yet another question!!! I am trying to use the Worksheet_change Event to call a macro that I have placed in the "ThisWorkbook" folder using Application.run. The problem I am having is that I do not want to use the specific name of the workbook in the statement because it can change since I am making a "Template". Any suggestions how I can call the Active workbook instead?!?! I have tried several itterations and nothing seems to work. I keep getting application failure. I assume it cannot find the macro.... Example: Worksheet name - myworksheet macro name - runme Application.run "'myworksheet.xls'!ThisWorkbook.runme" but replace "myworksheet.xls" with a generic statement Thank you! -- I am not where I intended to go, but I think I am where I am supposed to be! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling a public Macro from a Private Macro
Maybe I'm thinking incorrect...
I am using the Worksheet_change event to watch a cell in 1 of 10 sheets in my workbook. If the cell changes value (someone types a new value) I want to run the code "runme" located in the "ThisWorkbook" folder. I thought (probably incorrectly) that I could not use the "Call" command from private macro within a specific worksheet folder to call a macro in a different folder. I have tried: Call ThisWorkbook.runme but it comes up with "Run-time error '1004'" "Application-defined or object-defined error" Thank you! -- I am not where I intended to go, but I think I am where I am supposed to be! "Jim Thomlinson" wrote: In order to call procedures in ThisWorkbook from outside of the ThisWorkbook module you need to preface the call with ThisWorkbook. So for example in Module 1 to call a procedure in ThisWorkbook you need to use something like this... Call ThisWorkbook.RunMe I am unclear why you would be worried about which is the active workbook and using Application.Run? Are you trying to run a procedure located in one workbook on a different workbook? So are you wanting to run Book1.ThisWorkbook.RunMe on as sheet in book2? If so then that is a different beast all together. -- HTH... Jim Thomlinson "thewizz" wrote: Yet another question!!! I am trying to use the Worksheet_change Event to call a macro that I have placed in the "ThisWorkbook" folder using Application.run. The problem I am having is that I do not want to use the specific name of the workbook in the statement because it can change since I am making a "Template". Any suggestions how I can call the Active workbook instead?!?! I have tried several itterations and nothing seems to work. I keep getting application failure. I assume it cannot find the macro.... Example: Worksheet name - myworksheet macro name - runme Application.run "'myworksheet.xls'!ThisWorkbook.runme" but replace "myworksheet.xls" with a generic statement Thank you! -- I am not where I intended to go, but I think I am where I am supposed to be! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling a public Macro from a Private Macro
Jim,
Thank you for your help! It made me think which led me to the answer! It is working now, but for some reason I had to leave the word "Call" out to make it work. What I have is: ThisWorkbook.Runme And it run fine. -- I am not where I intended to go, but I think I am where I am supposed to be! "Jim Thomlinson" wrote: In order to call procedures in ThisWorkbook from outside of the ThisWorkbook module you need to preface the call with ThisWorkbook. So for example in Module 1 to call a procedure in ThisWorkbook you need to use something like this... Call ThisWorkbook.RunMe I am unclear why you would be worried about which is the active workbook and using Application.Run? Are you trying to run a procedure located in one workbook on a different workbook? So are you wanting to run Book1.ThisWorkbook.RunMe on as sheet in book2? If so then that is a different beast all together. -- HTH... Jim Thomlinson "thewizz" wrote: Yet another question!!! I am trying to use the Worksheet_change Event to call a macro that I have placed in the "ThisWorkbook" folder using Application.run. The problem I am having is that I do not want to use the specific name of the workbook in the statement because it can change since I am making a "Template". Any suggestions how I can call the Active workbook instead?!?! I have tried several itterations and nothing seems to work. I keep getting application failure. I assume it cannot find the macro.... Example: Worksheet name - myworksheet macro name - runme Application.run "'myworksheet.xls'!ThisWorkbook.runme" but replace "myworksheet.xls" with a generic statement Thank you! -- I am not where I intended to go, but I think I am where I am supposed to be! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling a public Macro from a Private Macro
hi
alternatively create a *.xla put your sub(byval sheetname as string) there and call it using the worksheet you want it to perform on hope it helps good luck "Otto Moehrbach" wrote: ActiveWorkbook is the active workbook. ThisWorkbook is the workbook that holds the macro that is running. Does that help? Otto "thewizz" wrote in message ... Yet another question!!! I am trying to use the Worksheet_change Event to call a macro that I have placed in the "ThisWorkbook" folder using Application.run. The problem I am having is that I do not want to use the specific name of the workbook in the statement because it can change since I am making a "Template". Any suggestions how I can call the Active workbook instead?!?! I have tried several itterations and nothing seems to work. I keep getting application failure. I assume it cannot find the macro.... Example: Worksheet name - myworksheet macro name - runme Application.run "'myworksheet.xls'!ThisWorkbook.runme" but replace "myworksheet.xls" with a generic statement Thank you! -- I am not where I intended to go, but I think I am where I am supposed to be! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| difference between a private sub and a public function? | Excel Discussion (Misc queries) | |||
| Calling a private macro | Excel Programming | |||
| Calling a private sub | Excel Programming | |||
| public but private variables | Excel Programming | |||
| Public subroutine called from a private sub | Excel Programming | |||
Linear Mode
