VBA open/close workbook problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mdc1952 - 26 Jul 2006 21:45 GMT

I'm creating a template that will be opened by users and saved to a new
name based on info provided by the user. I have macros triggered by
opening and closing the workbook. The before closing sub does some data
transfer between workseets. I'm running into a problem with the open
sub, however. I wanted to give the user a chance to abort the opening,
giving a dialog that allows canceling. I wanted to just close the
workbook. However, if I include a Workbook.Close line in the Open sub,
it of course triggers the before close sub, but seems to leave the On
Open sub hanging. The workbook closes, but the next time I open it, the
dialog in the Open sub doesn't come up. Close again, and it works ok or
quit Excel between openings and it is ok.

Is there some method I can use to cleanly get out of the On open sub
when I want to abort and still have a BeforeClose sub?

Reply to this Message

Bob Greenblatt - 26 Jul 2006 22:05 GMT

On 7/26/06 4:45 PM, in article
1153946707.519916.314030@i42g2000cwa.googlegroups.com, "mdc1952"
<michaelconner@missouristate.edu> wrote:

> I'm creating a template that will be opened by users and saved to a new
> name based on info provided by the user. I have macros triggered by
[quoted text clipped - 10 lines]
> Is there some method I can use to cleanly get out of the On open sub
> when I want to abort and still have a BeforeClose sub?

The reason this is happening is Excel's VBA stack. You'll need to set some
global flags and make sure the open sub has completed. You can test for the
flags in another sub or the close event sub.

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

mdc1952 - 26 Jul 2006 22:37 GMT

Thanks for the info. I figured it was something like this, but I'm not
into the plumbing of VBA. However, I still don't see how to accomplish
what I want.

If I let the Open macro complete, bypassing the remainder of the macro
if the user indcates canceling, how to I trigger closing within VBA? If
the Open macro completes without an Activeworkbook.close statement
things will just sit there until the user initiates closing.

Is there a way to terminate the open sub from within the close sub if I
create a flag to indicate closing started during the open sub?

Thanks for your help.

Reply to this Message

Bob Greenblatt - 27 Jul 2006 12:32 GMT

On 7/26/06 5:37 PM, in article
1153949843.460994.167900@p79g2000cwp.googlegroups.com, "mdc1952"
<michaelconner@missouristate.edu> wrote:

> Thanks for the info. I figured it was something like this, but I'm not
> into the plumbing of VBA. However, I still don't see how to accomplish
[quoted text clipped - 9 lines]
>
> Thanks for your help.

You could have the close sub set a flag that is examined in the open sub.

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

Mac Forum / Applications / Excel / July 2006

VBA open/close workbook problem