before close event

[mark]

I wrote an add-in which I load on the Workbook_Open event, and close on the
Workbook_BeforeClose event.

I just noticed that if you go to close the file, but then hit Cancel, so
you're still in the file, my Add-In has been unloaded.

This means that the BeforeClose event is firing prior to the pressing of the
Cancel button, which then keeps the workbook open.

It's not a huge deal, but, someone will likely see this as a bug, if I leave
it this way. Ideas on how to trap the press of the Cancel button, and reload
the Add-In?

I'm not sure that's most efficient, though... I'd rather not unload the
Add-In until the workbook is really going to close.

Ideas?
Thanks.

 

[Jim Thomlinson]

Instead of using the on open and on close events try using the Addin Install
and Addin Unistall events. They do not get fired on Cancel. Note that your
addin would not be alone in this category. There are lots of addins that fall
into this trap. Even commercial product such as Oracle's (Hyperion) Essbase
addin unloads in this manner.

 

[mark]

with you part way...

but, on the test I just did, the Add-In doesn't uninstall when I close the
workbook, which is what I want.

So, tracking back, I still need something to fire the Add-In Uninstall Event.

I noticed that the BeforeClose had a parameter (Cancel as Boolean) that I
wasn't using. I could trap that.

Or did I miss something in what you said?

 

[Jim Thomlinson]

Personally I haven't tried it, but it might be problematic. You are trying to
close XL but you are saying do not close the addin. That is what cancel does
is it allows you to end the close process. It has nothing to do with finding
out if the user hit cancel or not.

When you install an addin the install event fires and I use that to create
menu options and such. I use the uninstall to remove the menu options. While
I do use the open event to initialize variables and objects I do not use the
close event to destroy them. I just let them get destroyed when XL closes. My
uninstall also destroys them.

 

[mark]

Sounds like we're doing some slightly different things.

My Add-In doesn't put in any new menus, just some subroutines and functions
for database access to an Oracle database.

There is a connection to the database in there, which we want to have
available when a given workbook is open, and not available when that given
workbook is not open.

You wrote:

"You are trying to close XL but you are saying do not close the addin."

It's actually sort of the opposite of that. Whether or not I'm closing
Excel, isn't the question... just the workbook. If the workbook closes,
uninstall the Add-In. If the workbook opens, install the Add-In.... is what
is wanted (partially by management).

 

[Jim Thomlinson]

The before close event fires prior to the dialog that allows the user to
cancel. So it doesn not containg the user repsonse. Rather it allows you to
cancel the closing of the addin. If you cancel the closing and the user
choose to continue with exiting XL it may cause an issue. It may not but it
is something to watch out for. What are you using the before close event
for???