Hello all,

I have broken my question up into 2 parts. The quick scenario, and my
actual and lengthy one.

The quick summary of my problem is this:

I am running a macro from a workbook that is supposed to open another
workbook, and then close itself afterwards and do some other code as
well…however, whenever I close the original workbook that had the
code…all execution stops? I have tried everything I can think of, but
no luck yet.

In other words, if I open Workbook A and run a macro that has code to
open Workbook B then close Workbook A, and continue on with some more
code, the code execution stops after workbook A is closed. Has anyone
heard or come across this problem, and more importantly…a solution?

Thanks so much,
Rees


The much more in-depth background for those with time and need more
clarification:


My problem is this:

I have a macro attached to a toolbar that is created from the opening
of a template file. When a user clicks this button called "Create New
Job File" it eventually leads to opening a copy of the original
template file which then asks (via a Userform) what they want to do:
ie create a blank file or copy information from a file from the week
before. At this moment they will have the original file, that called
the macro (let's call it "Week34-Original") and a copy of the template
file (lets call it "Weekly File Template1") open only. Upon opening
"Weekly File Template1", it runs its Auto Open routine, which is to
display that userform. If the user chooses to create a new file, the
program will ask for the name and location to store it in. Naturally,
I need to be sure they are not saving the file as a name that already
exists or they will overwrite it, and herein lies my problem

If the file already exists, I am supposed to give them the option to
open that file if they would like. And since they are only supposed to
have one file open at a time (in order to keep some control and ease of
use), and hypothetically if they say yes to open the file, the program
will have to close 2 files and leave only the newly opened one open.
So, there are 3 files open at this moment as starting the macro opened
the template copy ("Weekly File Template1") which then asked if we
wanted to open another file, which we did (call it “Week34-New”), and,
of course, the original file (“Week34-Original”) open as well. All is
fine to here, but I want to continue the execution of that code that
opens the file to then close (in any order, the original file and the
template copy file). What always ends up happening, however, is that
whenever either the original file or the template file is closed, all
execution stops…kinda as if, the reason for the macro to run, no longer
exists. I have had this problem cropping up now and again, and now it
is driving me mad, as I have a deadline to reach. Any help would be
greatly appreciated.

Thanks, if anyone would like to see my code, let me know

Rees


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

From the quick summary:

Instead of closing the first workbook, you could hide it by setting the IsAddIn property of the Workbook to True, run the rest of your code, and then close it at the very end.

Thanks for the thoughts, however, the program requires that they have
only one workbook open at a time or it will cause other problems not to
mention way too many books open,

Thanks again for the quick reply though


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Use Application.OnTime to start the macro in the second workbook. This will
break the link between the code in the two workbooks.

Application.Ontime Now, "Workbook2.xls!Macroanme"

--
Regards,
Tom Ogilvy

"reesmacleod" wrote in message
...

Thanks for the thoughts, however, the program requires that they have
only one workbook open at a time or it will cause other problems not to
mention way too many books open,

Thanks again for the quick reply though


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Thanks for the reply Tom.

Not sure if I am able to implement that or not as this is a larg
program and I would have to have that command?...I am not too sur
where and how...but I will give it a shot.

Ree

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements