Calling Macro From Different Workbook

**Launchnet** · 03-22-2008, 12:23 AM

I have a workbook . . . named: MyExcel

And several other workbooks . . . named: A, B & C

I have MyExcel open and I open workbook A

Now, both workbooks are open with two instances of excel.

When I am using workbook A , Is there a way of using (calling) a macro from MyExcel or do I have to duplicate the macro in each workbook A B C ?

Thanks

Launchnet

**Portuga** · 03-22-2008, 01:08 AM

You can use the macro in the new book, without it being saved in any module.

As long as there is no references to the original document or sheet, (in this case you will get an error) it will replicate the macro you call in the active worksheet.

Click alt+F8 and select the macro you want and run it.

**Launchnet** · 03-22-2008, 02:48 PM

When I am in Workbook A and I use Alt F8, only the macros from Workbook A are shown. This is my problem.

What I need is a macro in MyExcel Workbook that I can run from Workbook A

Currently, I have to have the macro in all Workbooks in order for it to run.

Thanks

Launchnet

**Leith Ross** · 03-22-2008, 03:29 PM

Hello Launchnet,

If your macro is in a standard VBA module and the workbook that contains it is open, and as Portuga poited out, there are no references to the workbook that contains the macro, and it it is not declared as as Private, it will be available in the Macro List dialog (ALT+F8). You will need to select "All Workbooks" to see it. To run it in code, you will need to include the workbook name.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**Launchnet** · 03-22-2008, 04:05 PM

I have 2 instances of excel open at the same time.

I think this is the problem.

I can completely close one of the open excel programs and the other remains open.

I did select All Workbooks before I 1st asked for help and have tried again and it doesn't show any macros except those in the instance of excel that I have open.

Any other ideas would be appreciated.

Thanks
Launchnet

**Leith Ross** · 03-22-2008, 04:07 PM

Hello Launchnet,

Can you post your workbook for review?

Thanks,
Leith Ross

**Launchnet** · 03-22-2008, 09:08 PM

Leith:
All you need is two empty workbooks.
1st, MyExcel has a macro that closes whichever workbook(and the instance of Excel that holds the workbook) that is displayed, by clicking a button on that workbook, saving and closing the the open workbook, but not closing the MyExcel workbook.

Workbooks A & B & C & etc.
Here I would like a button on each workbook, that can use the Close Macro in the MyExcel workbook. Naturally, the application is considerably more than just this macro.

I can copy the macro to each workbook, but I just thought that maybe there was a way of using the macro from MyExcel workbook and not having to copy it to every other workbook, keeping overhead down a little and saving me a little work.

Thanks
Matt @ Launchnet

ProtonLeah:
If all workbooks were in one instance of Excel, the one macro would work.
My application works much better using individual instances of Excel.

ProtonLeah . . . Proton is a modified form of radiation which is used for different types of cancer treatment. I had Prostate cancer and considered Radiation, Cryo (freezing) and Proton. I choose Ultrasound which has been modified for Prostate Cancer. Every man should read my store at: www.openoursite.com Click on Keywords and then select "Matts Story". If you or any other man is over 30 years old, you really need to read this story.

p.s. On the front page of the site, you will see my Beautiful 1961 Red Chrysler, in near perfect condition. Needless to say, it is my favorite classic cars.

**Leith Ross** · 03-22-2008, 09:49 PM

Hello Matt,

Without seeing your code, I have no way to answer your questions. That's why asked to see your workbook. I can more quickly pinpoint problems relating to the macro or supporting code structures.

I did visit your site and read your story. Your are truly blessed. My father will be 81 in April. He has had 2 bouts of non Hodgekin's lymphoma and 1 bout with prostate cancer. Unlike you, he received the radiation treatments with a chemo followup. He has been fine. Like you, he too had people pray for him. I believe this is what help him bet the odds. Thanks for sharing your story.

Sincerely,
Leith Ross

**Launchnet** · 03-22-2008, 11:00 PM

Hi Again
The below code I would place in MyExcel workbook.

A button on open workbook "A" would some how address this code in MyExcel and would close and save workbook "A".

MyExcel workbook always remains open.

Please Login or Register  to view this content.

Matt

**Leith Ross** · 03-22-2008, 11:45 PM

Hello Matt,

The macro needs to be placed in a standard VBA module. It can then be called remotely. I modified your code to close a single workbook at a time and removed the code to close Excel.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Calling the Macro Remotely
Place this macro in a standard VBA module. Add a button to each workbook and attach the following macro. When the button is clicked it will save the workbook. MyExcel will remain open.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**Launchnet** · 03-23-2008, 07:03 PM

Thanks Leith Ross

By the way, my middle name is Ross.

I have decided to leave the code as I currently started with as it isn't that much longer than your suggestion. I had hoped that maybe there was some way of directly linking to the code, but I don't think there is since all the workbooks reside in a different instance of Excel.

Thanks much for your help. I truly enjoy the learning experience.

Matt @ Launchnet