VBA open/close workbook problem

[mdc1952]

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?

 

[Bob Greenblatt]

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?

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.

 

[mdc1952]

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.

 

[Bob Greenblatt]

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.

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