I have a workbook that invokes the Workbook_BeforeClose sub routine when
shutting down. I would like to change the behavior of this routine dependant
upon whether the closing of the workbook is associated with the shutting
down of Excel (version 2003).

Thanks in advance for any assistance with this question.

Ralph

The file would close before the application shuts down so I don't think it's
possible to do from inside the box.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"RFraley" wrote:
I have a workbook that invokes the Workbook_BeforeClose sub routine when
shutting down. I would like to change the behavior of this routine
dependant upon whether the closing of the workbook is associated with the
shutting down of Excel (version 2003).

Thanks in advance for any assistance with this question.

Ralph

Ralph,

The only way I can think of is to write a COM Add-In (CAI) and in the
AddinInstance_OnDisconnection event, test the value of RemoveMode. If it is
equal to ext_dm_HostShutdown ( = 0), then Excel is shutting down. If it is
< 0, then the user closed the add-in and Excel is not shutting down.

I just wrote up a quick and dirty COM Add-In in VB6 to produce a log of the
Excel environment (workbooks open, add-ins, etc) to test the order of what
is getting closed when, and the CAI is getting unloaded before workbooks are
closed. Thus, you could put code in the OnDisconnection event of a CAI to do
whatever needs to be done in your application when Excel shuts down.

If you want a copy of the VB6 code (you can also write CAIs in VBA using the
same source code as in VB6), send me an email. I'll probably write something
about this on my web site in the next day or two.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"RFraley" wrote in message
. ..
I have a workbook that invokes the Workbook_BeforeClose sub routine when
shutting down. I would like to change the behavior of this routine
dependant upon whether the closing of the workbook is associated with the
shutting down of Excel (version 2003).

Thanks in advance for any assistance with this question.

Ralph

I should have added that this is not a fool-proof method. The user could
unload your COM Add-In without closing the Application, and in that case
your CAI would not be loaded when Excel is shutdown. Therefore, of course,
you would be unable to detect when Excel is finally shutdown.

The efficacy of this solution depends largely on the sophistication of the
users, in reverse correlation. The less the user knows about Excel, the more
effective this solution will be, since a novice user is unlikely to even
know your CAI is loaded, let alone know how to unload it. A more advanced
user is more likely to be poking around various add-ins and more likely to
unload your CAI.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Chip Pearson" wrote in message
...
Ralph,

The only way I can think of is to write a COM Add-In (CAI) and in the
AddinInstance_OnDisconnection event, test the value of RemoveMode. If it
is equal to ext_dm_HostShutdown ( = 0), then Excel is shutting down. If it
is < 0, then the user closed the add-in and Excel is not shutting down.

I just wrote up a quick and dirty COM Add-In in VB6 to produce a log of
the Excel environment (workbooks open, add-ins, etc) to test the order of
what is getting closed when, and the CAI is getting unloaded before
workbooks are closed. Thus, you could put code in the OnDisconnection
event of a CAI to do whatever needs to be done in your application when
Excel shuts down.

If you want a copy of the VB6 code (you can also write CAIs in VBA using
the same source code as in VB6), send me an email. I'll probably write
something about this on my web site in the next day or two.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"RFraley" wrote in message
. ..
I have a workbook that invokes the Workbook_BeforeClose sub routine when
shutting down. I would like to change the behavior of this routine
dependant upon whether the closing of the workbook is associated with the
shutting down of Excel (version 2003).

Thanks in advance for any assistance with this question.

Ralph

The other side is I see a lot of novice users that always close the
file..... then the application. They always start with the inner most X then
progressively move out X-ing their way.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Chip Pearson" wrote:
I should have added that this is not a fool-proof method. The user could
unload your COM Add-In without closing the Application, and in that case
your CAI would not be loaded when Excel is shutdown. Therefore, of course,
you would be unable to detect when Excel is finally shutdown.

The efficacy of this solution depends largely on the sophistication of the
users, in reverse correlation. The less the user knows about Excel, the
more effective this solution will be, since a novice user is unlikely to
even know your CAI is loaded, let alone know how to unload it. A more
advanced user is more likely to be poking around various add-ins and more
likely to unload your CAI.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)