Opening in different workbook...

**DaPackers** · 01-06-2007, 10:02 AM

I currently have a workbook that, when opened, automatically kicks off a macro (with a call from the Workbook_Open event in ThisWorkbook). I was to run that same called macro from a custom button that I put on a toolbar in Excel itself...which is pretty straight forward. However, when I do this, the macro kicks off twice...once from the button and once because the called macro's workbook opens and kicks off the macro with the Workbook_Open event.

So, what I was thinking was...is there a way to tell if a workbook is being opened by another workbook (by calling it's macro) or whether is was opened "normally (manually)"?

Thanks in advance!

**MSP77079** · 01-06-2007, 12:43 PM

is there a way to tell if a workbook is being opened by another workbook

Not that I know of.

What you could do, for the code behind the toolbar button, is test to see if the workbook is already open. If so, then run the on-open macro. If not, simply open the workbook, knowing that it's on-open macro will run.

A more general answer would be: to prevent an on-open macro from running when you open a workbook using code, first set

Please Login or Register  to view this content.

then, open the workbook, then set

Please Login or Register  to view this content.

**DaPackers** · 01-06-2007, 01:54 PM

I think I understand what you are saying, but what I am doing with the toolbar custom button is simply adding it to the toolbar (i.e, with will show up whenever you open Excel), and the button runs the macro in the other workbook. In other words, the ONLY code behind the button is the macro being opened in the other workbook.

I'm new to adding custom buttons to the toolbar, so I hope this makes sense. If there's another way of doing this, PLEASE let me know!

Thanks again!

**MSP77079** · 01-06-2007, 02:19 PM

I hope this makes sense

Well, I am absolutely certain it makes sense. I just am not certain that I understand it yet.

Let me see if I get it ... you added a custom button to a standard toolbar by using View >> Toolbars >> Customize, adding a button, then assigning a macro. Or something very similar to this. When you assigned the macro, the workbook (let's call it Book1.xls) was open and you assigned to the button "Macro1" (for example).

Now, when you press the button, it opens the Book1.xls and runs "Macro1".

In addition to this, that Book1.xls has a Workbook_Open Event procedure that calls "Macro1".

Well, this all makes perfect sense now.

OK, there are several things you can do. The easiest (if you are the only person using the workbook) is to kill the Workbook_Open procedure completely. You don't need it anymore. But, I assume you thought of that already and it's not what you want to do.

Something that is incredibly simple to do, but difficult to explain would be the following (just trust me on this, OK?) ...

In Book1.xls, in a Module (maybe the same one that contains Macro1), put this as the very first line in the Module (before "Sub Macro1", for example):

Please Login or Register  to view this content.

As the first 2 lines of code in Macro1, add this:

Please Login or Register  to view this content.

What this will do is make it so that Macro1 cannot run more than once per session.

**MSP77079** · 01-06-2007, 02:33 PM

What this will do is make it so that Macro1 cannot run more than once per session.

The above might not be what you want, either. You probably added the button because you want to run "Macro1" pretty often. Just don't want to run it twice when the workbook first opens.

OK, here's what you might consider ...
1. ignore the posting above
2. Add Macro2

Please Login or Register  to view this content.

3. Add this line to the Workbook_Open routine:

Please Login or Register  to view this content.

4. Change the custom button on the toolbar so that it calls Macro2

What this does is:
1. when workbook opens, it sets "beenThere" to true, and runs Macro1
2. following that, the toolbar request to run Macro2 will not run Macro1; but, will "reset" the beenThere variable, so that
3. the next time you press the command bar button, Macro2 will call Macro1.

Hope this makes sense.

**DaPackers** · 01-06-2007, 03:14 PM

Ok, I believe I understand what you are getting at. However, I think you may have a discrepancy/type-o in what you presented in your posting.

You say:

2. following that, the toolbar request to run Macro2 will not run Macro1; but, will "reset" the beenThere variable, so that
3. the next time you press the command bar button, Macro2 will call Macro1.

But your code is:

Please Login or Register  to view this content.

Either what you SAY is incorrect, or what you coded is incorrect. I'm assuming the code should be something like:

Please Login or Register  to view this content.

Any comment?

**MSP77079** · 01-06-2007, 03:31 PM

You are correct. I left out something.

Your solution should work. Have you tried it?

**DaPackers** · 01-06-2007, 03:46 PM

It ALMOST works perfectly. Let me throw one more wrench into it...

The Macro1 closes the workbook (I'll call it "mac-book"), it's contained in, at the end of running. Basically, I do all my work on one of the worksheets in mac-book. Then, copy the results to a new workbook. Then, close mac-book without saving the changes.

Anyway, everything works great, except there is an error at the end stating that Macro2 is not found. I'm assuming that's because I've closed mac-book...because, if I comment out the Close, there is no error (but, mac-book is left open with all my changes in it...which is bad).

Any more ideas? I'm thinking of just going to the "easy" fix and just removing the call from Workbook_Open completely ;-)

**MSP77079** · 01-06-2007, 04:06 PM

I know of at least 2 other options.

Again ... assuming that you really want to have Macro1 whenever the workbook opens, but not to run twice.

Remove the Workbook_Open Event routine.
Remove all the "beenThere" done that stuff.
Add a macro and name it "Auto_Open".

Please Login or Register  to view this content.

This is an older method of running a macro when a workbook opens. But, when you open the workbook from the toolbar (or open the workbook from another VBA routine), Auto_Open does not run.

So, this allows you to preserve the "on open" feature, and also use the toolbar button.

It should work. I just tested it, and it seemed to do what you want.

I might as well tell you the second option, just in case this doesn't exactly fit the bill, and I get on to other work that I have been successfully avoiding. That would be to tie the toolbar button to a macro in your Personal.xls workbook (which you probably don't have set up, so you would need to set that up).

--------------- ADDED LATER -------------------
The Auto_Open macro goes in a Module.