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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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)

Chip,
Do you plan on adding to your site on this subject ?
I'm interested, as I'm sure others are too.

Thanks

NickHK

"Chip Pearson" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
> >
>
>

> Do you plan on adding to your site on this subject ?

It is presently in a "not ready for prime time" state. It will be there in a
day or two.


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



"NickHK" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Chip,
> Do you plan on adding to your site on this subject ?
> I'm interested, as I'm sure others are too.
>
> Thanks
>
> NickHK
>
> "Chip Pearson" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >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
>> >
>>
>>
>
>

Chip,
Whenever you're ready...

NickHK

"Chip Pearson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> > Do you plan on adding to your site on this subject ?
>
> It is presently in a "not ready for prime time" state. It will be there in
a
> day or two.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email address is on the web site)
>
>
>
> "NickHK" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Chip,
> > Do you plan on adding to your site on this subject ?
> > I'm interested, as I'm sure others are too.
> >
> > Thanks
> >
> > NickHK
> >
> > "Chip Pearson" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> >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
> >> >
> >>
> >>
> >
> >
>
>

> Whenever you're ready...

See http://www.cpearson.com/excel/ExcelShutdown.htm .


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


"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Chip,
> Whenever you're ready...
>
> NickHK
>
> "Chip Pearson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> > Do you plan on adding to your site on this subject ?
>>
>> It is presently in a "not ready for prime time" state. It will be there
>> in
> a
>> day or two.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>> (email address is on the web site)
>>
>>
>>
>> "NickHK" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > Chip,
>> > Do you plan on adding to your site on this subject ?
>> > I'm interested, as I'm sure others are too.
>> >
>> > Thanks
>> >
>> > NickHK
>> >
>> > "Chip Pearson" <(E-Mail Removed)> wrote in message
>> > news:%(E-Mail Removed)...
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> >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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Chip,
Ah...so that's how you do it.

Thanks

NickHK

"Chip Pearson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> > Whenever you're ready...
>
> See http://www.cpearson.com/excel/ExcelShutdown.htm .
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email address is on the web site)
>
>
> "NickHK" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Chip,
> > Whenever you're ready...
> >
> > NickHK
> >
> > "Chip Pearson" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> > Do you plan on adding to your site on this subject ?
> >>
> >> It is presently in a "not ready for prime time" state. It will be there
> >> in
> > a
> >> day or two.
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >> (email address is on the web site)
> >>
> >>
> >>
> >> "NickHK" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)...
> >> > Chip,
> >> > Do you plan on adding to your site on this subject ?
> >> > I'm interested, as I'm sure others are too.
> >> >
> >> > Thanks
> >> >
> >> > NickHK
> >> >
> >> > "Chip Pearson" <(E-Mail Removed)> wrote in message
> >> > news:%(E-Mail Removed)...
> >> >> 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" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> >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
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>