Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Sub in a Worksheet
This one is evading me. My macro opens another workbook and needs to call a
Public Sub located in a worksheet rather than a module. Is this even possible? If the macro were in a module, things would work fine but that is not the case here. Any help will be greatly appreciated. -- Best wishes, Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Sub in a Worksheet
Why can't you put it in a module? you can leave it in the sub, and copy it as
a function in a module -- -John Please rate when your question is answered to help us and others know what is helpful. "Jim Jackson" wrote: This one is evading me. My macro opens another workbook and needs to call a Public Sub located in a worksheet rather than a module. Is this even possible? If the macro were in a module, things would work fine but that is not the case here. Any help will be greatly appreciated. -- Best wishes, Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Sub in a Worksheet
Hi Jim,
This one seems to work for me. Opens a workbook then runs a public Sub named "Test" that's in the Sheet1 code module. Dim wb As Workbook Set wb = Workbooks.Open("D:\Temp\Book1.xls") Run wb.Name & "!Sheet1.Test" This way also works: Dim wb As Workbook Set wb = Workbooks.Open("D:\Temp\Book1.xls") wb.Sheets("Sheet1").Test -- Hope that helps. Vergel Adriano "Jim Jackson" wrote: This one is evading me. My macro opens another workbook and needs to call a Public Sub located in a worksheet rather than a module. Is this even possible? If the macro were in a module, things would work fine but that is not the case here. Any help will be greatly appreciated. -- Best wishes, Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling a Sub in a Worksheet
Vergel,
Thank you very much. It works perfectly. I could not make changes to the workbook itself since it is someone else's property so calling the macro from my own macro was the only choice other than manually clicking the button on the other one. Thanks again, both of you for prompt answers and good help. -- Best wishes, Jim "Vergel Adriano" wrote: Hi Jim, This one seems to work for me. Opens a workbook then runs a public Sub named "Test" that's in the Sheet1 code module. Dim wb As Workbook Set wb = Workbooks.Open("D:\Temp\Book1.xls") Run wb.Name & "!Sheet1.Test" This way also works: Dim wb As Workbook Set wb = Workbooks.Open("D:\Temp\Book1.xls") wb.Sheets("Sheet1").Test -- Hope that helps. Vergel Adriano "Jim Jackson" wrote: This one is evading me. My macro opens another workbook and needs to call a Public Sub located in a worksheet rather than a module. Is this even possible? If the macro were in a module, things would work fine but that is not the case here. Any help will be greatly appreciated. -- Best wishes, Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calling macros from worksheet to another | Excel Discussion (Misc queries) | |||
Calling another worksheet | Excel Programming | |||
vb.net calling worksheet by name | Excel Programming | |||
Calling a worksheet via a codename | Excel Programming | |||
Calling a different worksheet??? | Excel Programming |