Calling a Sub in a Worksheet

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.