Keeping a macro running after the calling workbook is closed (XL2K)

I run a macro in Wkbk1, the macro uses the Application.Run command to
call a macro in Wkbk2.

The second macro closes Wkbk1. As soon as Wkbk1 is closed the macro
stops running.

Is there a way to stop this from happening?

Thanks in advance

Why not just close the workbook after the other macro finishes?

application.run "book2.xls!othersub"
thisworkbook.close savechanges:=false 'true?

I didn't see a way to stop it from happening.

Simon C wrote:
>
> I run a macro in Wkbk1, the macro uses the Application.Run command to
> call a macro in Wkbk2.
>
> The second macro closes Wkbk1. As soon as Wkbk1 is closed the macro
> stops running.
>
> Is there a way to stop this from happening?
>
> Thanks in advance

--

Dave Peterson

Thanks for your input Dave

Sadly the problems a tad more complicated than the overly simplified
version I presented

If this no way to make xl 'remember' the code it's currently running
after it closes the workbook that called it I'll just have to find some
other workaround

Please post back when you find that workaround.

I couldn't think of anything that would work.

Without knowing the details, maybe you could have an OnTime macro close that
other workbook at the earliest time possible???

Simon C wrote:
>
> Thanks for your input Dave
>
> Sadly the problems a tad more complicated than the overly simplified
> version I presented
>
> If this no way to make xl 'remember' the code it's currently running
> after it closes the workbook that called it I'll just have to find some
> other workaround

--

Dave Peterson

Dave,

I found a solution but first I should explain a little bit more about
the problem...

I have a suite of 14 calculation wrkbks with a menu wrkbk containing a
userform and data that is shared by the 14 wrkbks.

The different menu options open 1 - 4 of the calculation wrkbks
depending on whcih calcualtions are required.

There was code to return to the menu in each wrkbk (but not actually
close any of the calcualtion wrkbks). The idea being that when the user
picks his next option from the menu it would then close the calculation
wrkbks it no longer needed and open any new wrkbks it did need (some of
the calculation wrkbks used more often than others and I didnt want it
closing wrkbks only to reopen them a few seconds later)

The problem was that if the calculation wrkbk that opened the menu was
closed the code stopped running abruptly.

Your solution would work for some cases but if it was one of the more
common calc wrkbks or if the user needed to do the same calc twice in a
row it would bring up the menu open and close all the required calc
wrkbks and then return to the origanal and promptly close a wrkbk it
had just opened

The solution was to take the code for returning to the menu out of the
calc wrkbks and into a userform and show that form modeless on top of
the calculation books. Not only does this solve my problem but it means
that all my code is in one wrkbk which will make for easier maintenance
in the future

Hope this makes sense!

Simon

Glad you found a solution that works for you. And thanks for posting back.

Simon C wrote:
>
> Dave,
>
> I found a solution but first I should explain a little bit more about
> the problem...
>
> I have a suite of 14 calculation wrkbks with a menu wrkbk containing a
> userform and data that is shared by the 14 wrkbks.
>
> The different menu options open 1 - 4 of the calculation wrkbks
> depending on whcih calcualtions are required.
>
> There was code to return to the menu in each wrkbk (but not actually
> close any of the calcualtion wrkbks). The idea being that when the user
> picks his next option from the menu it would then close the calculation
> wrkbks it no longer needed and open any new wrkbks it did need (some of
> the calculation wrkbks used more often than others and I didnt want it
> closing wrkbks only to reopen them a few seconds later)
>
> The problem was that if the calculation wrkbk that opened the menu was
> closed the code stopped running abruptly.
>
> Your solution would work for some cases but if it was one of the more
> common calc wrkbks or if the user needed to do the same calc twice in a
> row it would bring up the menu open and close all the required calc
> wrkbks and then return to the origanal and promptly close a wrkbk it
> had just opened
>
> The solution was to take the code for returning to the menu out of the
> calc wrkbks and into a userform and show that form modeless on top of
> the calculation books. Not only does this solve my problem but it means
> that all my code is in one wrkbk which will make for easier maintenance
> in the future
>
> Hope this makes sense!
>
> Simon

--

Dave Peterson