How can I detect that the user canceled a close?
 

That is all I need to know: Just how can I obtain what is probably a boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

"user canceled a close" - do you mean if user cancels Excel-quit or merely
while doing file-close, that might be relevant depending on your ultimate
objective. However in either case there is no direct method to return your
boolean if user cancelled close during the 'save unsaved file(s) prompt'.

I assume you want to know so as to determine whether or not to run your
close event code. One workaround is to run the close event as normal, but
also call a routine with the OnTime method to restore as-was. Eg with an
addins, might want to remove all menus as normal in the close event then put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage that its
close event only fires when Excel really is about to quit and won't be
cancelled. There are various ways to get the CAI to call a routine in the wb
to run close code 'only' if Excel and hence the file is about to close. I
believe Chip Pearson has made available on his site a CAI to do that, which
makes use of the 'hidden namespace'. I have a CAI that does similar but with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is probably a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

Peter T:

Thank you for responding to my plea for help. I should have spelled out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose event
procedure. But when the user closes and then clicks "Cancel" when asked to
Save, the Close is terminated and the toolbar is gone. I want to restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or merely
while doing file-close, that might be relevant depending on your ultimate
objective. However in either case there is no direct method to return your
boolean if user cancelled close during the 'save unsaved file(s) prompt'.

I assume you want to know so as to determine whether or not to run your
close event code. One workaround is to run the close event as normal, but
also call a routine with the OnTime method to restore as-was. Eg with an
addins, might want to remove all menus as normal in the close event then put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage that its
close event only fires when Excel really is about to quit and won't be
cancelled. There are various ways to get the CAI to call a routine in the wb
to run close code 'only' if Excel and hence the file is about to close. I
believe Chip Pearson has made available on his site a CAI to do that, which
makes use of the 'hidden namespace'. I have a CAI that does similar but with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is probably a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

Afraid all I can do is reiterate my previous response which, as it turns
out, appears to have fully anticipated your scenario and objective -

Can I somehow get a boolean from WorkbookBeforeSave?

No, neither can you get it (user cancelled close) from any other event, at
least not directly.

I want to restore that toolbar, or not delete it in the first place.

Try the workaround I suggested previously -
If working with VBA only, you will need to let your close event code run as
normally, ie delete your Toolbar. Then call your routine to add or restore
your toolbar with the Ontime method and a small delay. Five seconds should
be plenty, doesn't matter if user hangs around for more than that as it will
fire when allowed, assuming of course the workbook is still open, ie user
cancelled close.
Eg, add the following in your close event -

' in Workbook_BeforeClose or sub auto_close
Dim sMacro as string

code to delete toolbar, or call relevant routine to do that

sMacro = "AddToolBar"
sMacro = "'" & ThisWorkbook.Name & "'!" & sMacro
Application.Ontime Now + timeserial(0,0,5) sMacro ' adjust the 5 second
delay to suit

Put Sub AddToolBar() in a normal module with code to add or restore your
toolbar. This might be the same routine as called in the Open event. You can
include additional arguments in the macro string if necessary.

Alternatively try Chip Pearson's Com-addin
http://www.cpearson.com/excel/ExcelShutdown.htm
or as I mentioned I also have a Com-addin that employs some different
methods you're welcome to try if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose event
procedure. But when the user closes and then clicks "Cancel" when asked
to
Save, the Close is terminated and the toolbar is gone. I want to restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run your
close event code. One workaround is to run the close event as normal,
but
also call a routine with the OnTime method to restore as-was. Eg with an
addins, might want to remove all menus as normal in the close event then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage that
its
close event only fires when Excel really is about to quit and won't be
cancelled. There are various ways to get the CAI to call a routine in
the wb
to run close code 'only' if Excel and hence the file is about to close.
I
believe Chip Pearson has made available on his site a CAI to do that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is probably a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

To Peter T:

Thanks for your help.


--
TomThumb


"Peter T" wrote:

Afraid all I can do is reiterate my previous response which, as it turns
out, appears to have fully anticipated your scenario and objective -

Can I somehow get a boolean from WorkbookBeforeSave?

No, neither can you get it (user cancelled close) from any other event, at
least not directly.

I want to restore that toolbar, or not delete it in the first place.

Try the workaround I suggested previously -
If working with VBA only, you will need to let your close event code run as
normally, ie delete your Toolbar. Then call your routine to add or restore
your toolbar with the Ontime method and a small delay. Five seconds should
be plenty, doesn't matter if user hangs around for more than that as it will
fire when allowed, assuming of course the workbook is still open, ie user
cancelled close.
Eg, add the following in your close event -

' in Workbook_BeforeClose or sub auto_close
Dim sMacro as string

code to delete toolbar, or call relevant routine to do that

sMacro = "AddToolBar"
sMacro = "'" & ThisWorkbook.Name & "'!" & sMacro
Application.Ontime Now + timeserial(0,0,5) sMacro ' adjust the 5 second
delay to suit

Put Sub AddToolBar() in a normal module with code to add or restore your
toolbar. This might be the same routine as called in the Open event. You can
include additional arguments in the macro string if necessary.

Alternatively try Chip Pearson's Com-addin
http://www.cpearson.com/excel/ExcelShutdown.htm
or as I mentioned I also have a Com-addin that employs some different
methods you're welcome to try if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose event
procedure. But when the user closes and then clicks "Cancel" when asked
to
Save, the Close is terminated and the toolbar is gone. I want to restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run your
close event code. One workaround is to run the close event as normal,
but
also call a routine with the OnTime method to restore as-was. Eg with an
addins, might want to remove all menus as normal in the close event then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage that
its
close event only fires when Excel really is about to quit and won't be
cancelled. There are various ways to get the CAI to call a routine in
the wb
to run close code 'only' if Excel and hence the file is about to close.
I
believe Chip Pearson has made available on his site a CAI to do that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is probably a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

TomThumb,

Instead of the Open and BeforeClose events, look at using Activate and
Deactivate instead. When a workbook closes the Deactivate event fires after
the BeforeClose and the Cancel message. Also, then your toolbar will be
deleted when you switch to another workbook, which I'm guessing is also what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose event
procedure. But when the user closes and then clicks "Cancel" when asked
to
Save, the Close is terminated and the toolbar is gone. I want to restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your ultimate
objective. However in either case there is no direct method to return
your
boolean if user cancelled close during the 'save unsaved file(s) prompt'.

I assume you want to know so as to determine whether or not to run your
close event code. One workaround is to run the close event as normal, but
also call a routine with the OnTime method to restore as-was. Eg with an
addins, might want to remove all menus as normal in the close event then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage that
its
close event only fires when Excel really is about to quit and won't be
cancelled. There are various ways to get the CAI to call a routine in the
wb
to run close code 'only' if Excel and hence the file is about to close. I
believe Chip Pearson has made available on his site a CAI to do that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is probably a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

Hi Doug,

The Deactivate & Activate events are not triggered in an addin, which as I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate and
Deactivate instead. When a workbook closes the Deactivate event fires
after
the BeforeClose and the Cancel message. Also, then your toolbar will be
deleted when you switch to another workbook, which I'm guessing is also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose event
procedure. But when the user closes and then clicks "Cancel" when asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run your
close event code. One workaround is to run the close event as normal,
but
also call a routine with the OnTime method to restore as-was. Eg with
an
addins, might want to remove all menus as normal in the close event
then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage that
its
close event only fires when Excel really is about to quit and won't be
cancelled. There are various ways to get the CAI to call a routine in
the
wb
to run close code 'only' if Excel and hence the file is about to close.
I
believe Chip Pearson has made available on his site a CAI to do that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is probably a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

Peter T,

Then why is the user being prompted to save changes? I thought it was the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin, which as I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate and
Deactivate instead. When a workbook closes the Deactivate event fires
after
the BeforeClose and the Cancel message. Also, then your toolbar will be
deleted when you switch to another workbook, which I'm guessing is also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose event
procedure. But when the user closes and then clicks "Cancel" when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run
your
close event code. One workaround is to run the close event as normal,
but
also call a routine with the OnTime method to restore as-was. Eg with
an
addins, might want to remove all menus as normal in the close event
then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage
that
its
close event only fires when Excel really is about to quit and won't be
cancelled. There are various ways to get the CAI to call a routine in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

Doug & Peter T:

The WorkbookDeactivate event procedure did not remove the toolbar. The
Ontime works, but is problematic as I did get an error message once.

I need to get closer to close than WorkbookBeforeClose.

--
TomThumb


"Doug Glancy" wrote:

Peter T,

Then why is the user being prompted to save changes? I thought it was the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin, which as I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate and
Deactivate instead. When a workbook closes the Deactivate event fires
after
the BeforeClose and the Cancel message. Also, then your toolbar will be
deleted when you switch to another workbook, which I'm guessing is also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose event
procedure. But when the user closes and then clicks "Cancel" when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run
your
close event code. One workaround is to run the close event as normal,
but
also call a routine with the OnTime method to restore as-was. Eg with
an
addins, might want to remove all menus as normal in the close event
then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage
that
its
close event only fires when Excel really is about to quit and won't be
cancelled. There are various ways to get the CAI to call a routine in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

Hi again,

I thought it was the situation where the toolbar is created
at workbook open and deleted at close.

I assumed same, turned out to be toolbar in an addin.

Then why is the user being prompted to save changes?

Occurs if user attempts to close Excel while there are any unsaved workbooks
open. Concerning addins, in previous testing I found the Auto_close routine
always fires before user get the save changes prompt, but inconsistently
(not sure why) the BeforeClose event may fire either before or after user
gets the save changes prompt. The Toolbar will get deleted if called in the
auto_close and may get deleted if called in BeforeClose. The deactivate
event is not triggered in the addin.

Regards,
Peter T

"Doug Glancy" wrote in message
...
Peter T,

Then why is the user being prompted to save changes? I thought it was the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin, which as
I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate and
Deactivate instead. When a workbook closes the Deactivate event fires
after
the BeforeClose and the Cancel message. Also, then your toolbar will
be
deleted when you switch to another workbook, which I'm guessing is also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose
event
procedure. But when the user closes and then clicks "Cancel" when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to
return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run
your
close event code. One workaround is to run the close event as
normal,
but
also call a routine with the OnTime method to restore as-was. Eg
with
an
addins, might want to remove all menus as normal in the close event
then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage
that
its
close event only fires when Excel really is about to quit and won't
be
cancelled. There are various ways to get the CAI to call a routine
in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do
that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is
probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

The OnTime method to restore if user cancels close works fine for me
Do you know where you got the error message and why.

Regards,
Peter T

"TomThumb" wrote in message
...
Doug & Peter T:

The WorkbookDeactivate event procedure did not remove the toolbar. The
Ontime works, but is problematic as I did get an error message once.

I need to get closer to close than WorkbookBeforeClose.

--
TomThumb


"Doug Glancy" wrote:

Peter T,

Then why is the user being prompted to save changes? I thought it was
the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin, which
as I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate
and
Deactivate instead. When a workbook closes the Deactivate event
fires
after
the BeforeClose and the Cancel message. Also, then your toolbar will
be
deleted when you switch to another workbook, which I'm guessing is
also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have
spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose
event
procedure. But when the user closes and then clicks "Cancel" when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit
or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to
return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run
your
close event code. One workaround is to run the close event as
normal,
but
also call a routine with the OnTime method to restore as-was. Eg
with
an
addins, might want to remove all menus as normal in the close
event
then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage
that
its
close event only fires when Excel really is about to quit and
won't be
cancelled. There are various ways to get the CAI to call a routine
in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do
that,
which
makes use of the 'hidden namespace'. I have a CAI that does
similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is
probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

Peter,

Thanks, you're right. I had misunderstood.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi again,

I thought it was the situation where the toolbar is created
at workbook open and deleted at close.

I assumed same, turned out to be toolbar in an addin.

Then why is the user being prompted to save changes?

Occurs if user attempts to close Excel while there are any unsaved
workbooks
open. Concerning addins, in previous testing I found the Auto_close
routine
always fires before user get the save changes prompt, but inconsistently
(not sure why) the BeforeClose event may fire either before or after user
gets the save changes prompt. The Toolbar will get deleted if called in
the
auto_close and may get deleted if called in BeforeClose. The deactivate
event is not triggered in the addin.

Regards,
Peter T

"Doug Glancy" wrote in message
...
Peter T,

Then why is the user being prompted to save changes? I thought it was
the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin, which
as
I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate and
Deactivate instead. When a workbook closes the Deactivate event fires
after
the BeforeClose and the Cancel message. Also, then your toolbar will
be
deleted when you switch to another workbook, which I'm guessing is
also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose
event
procedure. But when the user closes and then clicks "Cancel" when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to
return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run
your
close event code. One workaround is to run the close event as
normal,
but
also call a routine with the OnTime method to restore as-was. Eg
with
an
addins, might want to remove all menus as normal in the close event
then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage
that
its
close event only fires when Excel really is about to quit and won't
be
cancelled. There are various ways to get the CAI to call a routine
in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do
that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is
probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

TomThumb,

If I understand correctly now, you've got an addin that creates a toolbar
when the addin Opens and deletes it at the addin's BeforeClose event.

If this is correct, I think you can just create the toolbar with
Temporary:=True and forget about deleting it when the addin closes. The
only time the addin should close is if it's unchecked in ToolsAddins or
when Excel closes. This way the toolbar will be automatically deleted when
Excel closes. You would also want to add the delete toolbar code to the
AddinUninstall event, so that it would be deleted if the addin is unchecked
in the ToolsAddin menu.

I'm probably missing something, but thought I'd try one more time to help,

Doug

"TomThumb" wrote in message
...
Doug & Peter T:

The WorkbookDeactivate event procedure did not remove the toolbar. The
Ontime works, but is problematic as I did get an error message once.

I need to get closer to close than WorkbookBeforeClose.

--
TomThumb


"Doug Glancy" wrote:

Peter T,

Then why is the user being prompted to save changes? I thought it was
the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin, which
as I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate and
Deactivate instead. When a workbook closes the Deactivate event fires
after
the BeforeClose and the Cancel message. Also, then your toolbar will
be
deleted when you switch to another workbook, which I'm guessing is
also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose
event
procedure. But when the user closes and then clicks "Cancel" when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to
return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run
your
close event code. One workaround is to run the close event as
normal,
but
also call a routine with the OnTime method to restore as-was. Eg
with
an
addins, might want to remove all menus as normal in the close event
then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage
that
its
close event only fires when Excel really is about to quit and won't
be
cancelled. There are various ways to get the CAI to call a routine
in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do
that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is
probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

That looks like an excellent solution for TomThumb.
Not sure why I didn't think of suggesting similar myself !.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

If I understand correctly now, you've got an addin that creates a toolbar
when the addin Opens and deletes it at the addin's BeforeClose event.

If this is correct, I think you can just create the toolbar with
Temporary:=True and forget about deleting it when the addin closes. The
only time the addin should close is if it's unchecked in ToolsAddins or
when Excel closes. This way the toolbar will be automatically deleted
when
Excel closes. You would also want to add the delete toolbar code to the
AddinUninstall event, so that it would be deleted if the addin is
unchecked
in the ToolsAddin menu.

I'm probably missing something, but thought I'd try one more time to help,

Doug

"TomThumb" wrote in message
...
Doug & Peter T:

The WorkbookDeactivate event procedure did not remove the toolbar. The
Ontime works, but is problematic as I did get an error message once.

I need to get closer to close than WorkbookBeforeClose.

--
TomThumb


"Doug Glancy" wrote:

Peter T,

Then why is the user being prompted to save changes? I thought it was
the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin, which
as I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate
and
Deactivate instead. When a workbook closes the Deactivate event
fires
after
the BeforeClose and the Cancel message. Also, then your toolbar
will
be
deleted when you switch to another workbook, which I'm guessing is
also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have
spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose
event
procedure. But when the user closes and then clicks "Cancel" when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit
or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to
return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to
run
your
close event code. One workaround is to run the close event as
normal,
but
also call a routine with the OnTime method to restore as-was. Eg
with
an
addins, might want to remove all menus as normal in the close
event
then
put
them back again a few seconds later if the workbook is still
open.

A different approach is to use a Com-addin. This has the
advantage
that
its
close event only fires when Excel really is about to quit and
won't
be
cancelled. There are various ways to get the CAI to call a
routine
in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do
that,
which
makes use of the 'hidden namespace'. I have a CAI that does
similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is
probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

I thank you guys for your help, but I don't think I stated the problem
clearly enough, so I will try again:

I create a toolbar on WorkbookOpen. I delete the toolbar on
WorkBookBeforeClose. The problem is that if the user cancels the prompt to
Save their workbook after WorkBookBeforeClose has already deleted the
toolbar, I end up with a workbook that is not closed and no toolbar. Surely
other people have this problem.

--
TomThumb


"Doug Glancy" wrote:

Peter,

Thanks, you're right. I had misunderstood.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi again,

I thought it was the situation where the toolbar is created
at workbook open and deleted at close.

I assumed same, turned out to be toolbar in an addin.

Then why is the user being prompted to save changes?

Occurs if user attempts to close Excel while there are any unsaved
workbooks
open. Concerning addins, in previous testing I found the Auto_close
routine
always fires before user get the save changes prompt, but inconsistently
(not sure why) the BeforeClose event may fire either before or after user
gets the save changes prompt. The Toolbar will get deleted if called in
the
auto_close and may get deleted if called in BeforeClose. The deactivate
event is not triggered in the addin.

Regards,
Peter T

"Doug Glancy" wrote in message
...
Peter T,

Then why is the user being prompted to save changes? I thought it was
the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin, which
as
I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate and
Deactivate instead. When a workbook closes the Deactivate event fires
after
the BeforeClose and the Cancel message. Also, then your toolbar will
be
deleted when you switch to another workbook, which I'm guessing is
also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose
event
procedure. But when the user closes and then clicks "Cancel" when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to
return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run
your
close event code. One workaround is to run the close event as
normal,
but
also call a routine with the OnTime method to restore as-was. Eg
with
an
addins, might want to remove all menus as normal in the close event
then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage
that
its
close event only fires when Excel really is about to quit and won't
be
cancelled. There are various ways to get the CAI to call a routine
in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do
that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is
probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

Peter T,

Thanks!

Doug

"Peter T" <peter_t@discussions wrote in message
...
That looks like an excellent solution for TomThumb.
Not sure why I didn't think of suggesting similar myself !.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

If I understand correctly now, you've got an addin that creates a toolbar
when the addin Opens and deletes it at the addin's BeforeClose event.

If this is correct, I think you can just create the toolbar with
Temporary:=True and forget about deleting it when the addin closes. The
only time the addin should close is if it's unchecked in ToolsAddins or
when Excel closes. This way the toolbar will be automatically deleted
when
Excel closes. You would also want to add the delete toolbar code to the
AddinUninstall event, so that it would be deleted if the addin is
unchecked
in the ToolsAddin menu.

I'm probably missing something, but thought I'd try one more time to
help,

Doug

"TomThumb" wrote in message
...
Doug & Peter T:

The WorkbookDeactivate event procedure did not remove the toolbar. The
Ontime works, but is problematic as I did get an error message once.

I need to get closer to close than WorkbookBeforeClose.

--
TomThumb


"Doug Glancy" wrote:

Peter T,

Then why is the user being prompted to save changes? I thought it was
the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin,
which
as I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate
and
Deactivate instead. When a workbook closes the Deactivate event
fires
after
the BeforeClose and the Cancel message. Also, then your toolbar
will
be
deleted when you switch to another workbook, which I'm guessing is
also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have
spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose
event
procedure. But when the user closes and then clicks "Cancel"
when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit
or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to
return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to
run
your
close event code. One workaround is to run the close event as
normal,
but
also call a routine with the OnTime method to restore as-was. Eg
with
an
addins, might want to remove all menus as normal in the close
event
then
put
them back again a few seconds later if the workbook is still
open.

A different approach is to use a Com-addin. This has the
advantage
that
its
close event only fires when Excel really is about to quit and
won't
be
cancelled. There are various ways to get the CAI to call a
routine
in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do
that,
which
makes use of the 'hidden namespace'. I have a CAI that does
similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is
probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

TomThumb,

You are right. This is a common problem and I have solved it for my own
work for both addins and xls files.

I am confused which applies to you . Is the workbook that you refer to an
xls or an addin? If it's an addin, I think the solution I proposed above,
i.e. create the toolbar as Temporary := True and use AddinInstall and
AddinUninstall, will work. If it's an xls, then then using deactivate
instead of BeforeClose should work.

Can you please say again which it is?

Doug

"TomThumb" wrote in message
...
I thank you guys for your help, but I don't think I stated the problem
clearly enough, so I will try again:

I create a toolbar on WorkbookOpen. I delete the toolbar on
WorkBookBeforeClose. The problem is that if the user cancels the prompt
to
Save their workbook after WorkBookBeforeClose has already deleted the
toolbar, I end up with a workbook that is not closed and no toolbar.
Surely
other people have this problem.

--
TomThumb


"Doug Glancy" wrote:

Peter,

Thanks, you're right. I had misunderstood.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi again,

I thought it was the situation where the toolbar is created
at workbook open and deleted at close.

I assumed same, turned out to be toolbar in an addin.

Then why is the user being prompted to save changes?

Occurs if user attempts to close Excel while there are any unsaved
workbooks
open. Concerning addins, in previous testing I found the Auto_close
routine
always fires before user get the save changes prompt, but
inconsistently
(not sure why) the BeforeClose event may fire either before or after
user
gets the save changes prompt. The Toolbar will get deleted if called in
the
auto_close and may get deleted if called in BeforeClose. The deactivate
event is not triggered in the addin.

Regards,
Peter T

"Doug Glancy" wrote in message
...
Peter T,

Then why is the user being prompted to save changes? I thought it was
the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin,
which
as
I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate
and
Deactivate instead. When a workbook closes the Deactivate event
fires
after
the BeforeClose and the Cancel message. Also, then your toolbar
will
be
deleted when you switch to another workbook, which I'm guessing is
also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have
spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose
event
procedure. But when the user closes and then clicks "Cancel"
when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit
or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to
return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to
run
your
close event code. One workaround is to run the close event as
normal,
but
also call a routine with the OnTime method to restore as-was. Eg
with
an
addins, might want to remove all menus as normal in the close
event
then
put
them back again a few seconds later if the workbook is still
open.

A different approach is to use a Com-addin. This has the
advantage
that
its
close event only fires when Excel really is about to quit and
won't
be
cancelled. There are various ways to get the CAI to call a
routine
in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do
that,
which
makes use of the 'hidden namespace'. I have a CAI that does
similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is
probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

XLS
--
TomThumb


"Doug Glancy" wrote:

TomThumb,

You are right. This is a common problem and I have solved it for my own
work for both addins and xls files.

I am confused which applies to you . Is the workbook that you refer to an
xls or an addin? If it's an addin, I think the solution I proposed above,
i.e. create the toolbar as Temporary := True and use AddinInstall and
AddinUninstall, will work. If it's an xls, then then using deactivate
instead of BeforeClose should work.

Can you please say again which it is?

Doug

"TomThumb" wrote in message
...
I thank you guys for your help, but I don't think I stated the problem
clearly enough, so I will try again:

I create a toolbar on WorkbookOpen. I delete the toolbar on
WorkBookBeforeClose. The problem is that if the user cancels the prompt
to
Save their workbook after WorkBookBeforeClose has already deleted the
toolbar, I end up with a workbook that is not closed and no toolbar.
Surely
other people have this problem.

--
TomThumb


"Doug Glancy" wrote:

Peter,

Thanks, you're right. I had misunderstood.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi again,

I thought it was the situation where the toolbar is created
at workbook open and deleted at close.

I assumed same, turned out to be toolbar in an addin.

Then why is the user being prompted to save changes?

Occurs if user attempts to close Excel while there are any unsaved
workbooks
open. Concerning addins, in previous testing I found the Auto_close
routine
always fires before user get the save changes prompt, but
inconsistently
(not sure why) the BeforeClose event may fire either before or after
user
gets the save changes prompt. The Toolbar will get deleted if called in
the
auto_close and may get deleted if called in BeforeClose. The deactivate
event is not triggered in the addin.

Regards,
Peter T

"Doug Glancy" wrote in message
...
Peter T,

Then why is the user being prompted to save changes? I thought it was
the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin,
which
as
I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate
and
Deactivate instead. When a workbook closes the Deactivate event
fires
after
the BeforeClose and the Cancel message. Also, then your toolbar
will
be
deleted when you switch to another workbook, which I'm guessing is
also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have
spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose
event
procedure. But when the user closes and then clicks "Cancel"
when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit
or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to
return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to
run
your
close event code. One workaround is to run the close event as
normal,
but
also call a routine with the OnTime method to restore as-was. Eg
with
an
addins, might want to remove all menus as normal in the close
event
then
put
them back again a few seconds later if the workbook is still
open.

A different approach is to use a Com-addin. This has the
advantage
that
its
close event only fires when Excel really is about to quit and
won't
be
cancelled. There are various ways to get the CAI to call a
routine
in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do
that,
which
makes use of the 'hidden namespace'. I have a CAI that does
similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is
probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

I can't even get the following code to execute:

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
MsgBox "WorkbookDeactivate"
End Sub

--
TomThumb


"TomThumb" wrote:

XLS
--
TomThumb


"Doug Glancy" wrote:

TomThumb,

You are right. This is a common problem and I have solved it for my own
work for both addins and xls files.

I am confused which applies to you . Is the workbook that you refer to an
xls or an addin? If it's an addin, I think the solution I proposed above,
i.e. create the toolbar as Temporary := True and use AddinInstall and
AddinUninstall, will work. If it's an xls, then then using deactivate
instead of BeforeClose should work.

Can you please say again which it is?

Doug

"TomThumb" wrote in message
...
I thank you guys for your help, but I don't think I stated the problem
clearly enough, so I will try again:

I create a toolbar on WorkbookOpen. I delete the toolbar on
WorkBookBeforeClose. The problem is that if the user cancels the prompt
to
Save their workbook after WorkBookBeforeClose has already deleted the
toolbar, I end up with a workbook that is not closed and no toolbar.
Surely
other people have this problem.

--
TomThumb


"Doug Glancy" wrote:

Peter,

Thanks, you're right. I had misunderstood.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi again,

I thought it was the situation where the toolbar is created
at workbook open and deleted at close.

I assumed same, turned out to be toolbar in an addin.

Then why is the user being prompted to save changes?

Occurs if user attempts to close Excel while there are any unsaved
workbooks
open. Concerning addins, in previous testing I found the Auto_close
routine
always fires before user get the save changes prompt, but
inconsistently
(not sure why) the BeforeClose event may fire either before or after
user
gets the save changes prompt. The Toolbar will get deleted if called in
the
auto_close and may get deleted if called in BeforeClose. The deactivate
event is not triggered in the addin.

Regards,
Peter T

"Doug Glancy" wrote in message
...
Peter T,

Then why is the user being prompted to save changes? I thought it was
the
situation where the toolbar is created at workbook open and deleted at
close.

Doug

"Peter T" <peter_t@discussions wrote in message
...
Hi Doug,

The Deactivate & Activate events are not triggered in an addin,
which
as
I
understand is what the OP wants to cater for.

Regards,
Peter T

"Doug Glancy" wrote in message
...
TomThumb,

Instead of the Open and BeforeClose events, look at using Activate
and
Deactivate instead. When a workbook closes the Deactivate event
fires
after
the BeforeClose and the Cancel message. Also, then your toolbar
will
be
deleted when you switch to another workbook, which I'm guessing is
also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have
spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose
event
procedure. But when the user closes and then clicks "Cancel"
when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit
or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to
return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to
run
your
close event code. One workaround is to run the close event as
normal,
but
also call a routine with the OnTime method to restore as-was. Eg
with
an
addins, might want to remove all menus as normal in the close
event
then
put
them back again a few seconds later if the workbook is still
open.

A different approach is to use a Com-addin. This has the
advantage
that
its
close event only fires when Excel really is about to quit and
won't
be
cancelled. There are various ways to get the CAI to call a
routine
in
the
wb
to run close code 'only' if Excel and hence the file is about to
close.
I
believe Chip Pearson has made available on his site a CAI to do
that,
which
makes use of the 'hidden namespace'. I have a CAI that does
similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is
probably
a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

Thanks, Doug, the following code did what I wanted:

Private Sub Workbook_Deactivate()
CreateToolBar
Application.CommandBars(Version).Delete
End Sub

--
TomThumb


"Doug Glancy" wrote:

TomThumb,

Instead of the Open and BeforeClose events, look at using Activate and
Deactivate instead. When a workbook closes the Deactivate event fires after
the BeforeClose and the Cancel message. Also, then your toolbar will be
deleted when you switch to another workbook, which I'm guessing is also what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose event
procedure. But when the user closes and then clicks "Cancel" when asked
to
Save, the Close is terminated and the toolbar is gone. I want to restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your ultimate
objective. However in either case there is no direct method to return
your
boolean if user cancelled close during the 'save unsaved file(s) prompt'.

I assume you want to know so as to determine whether or not to run your
close event code. One workaround is to run the close event as normal, but
also call a routine with the OnTime method to restore as-was. Eg with an
addins, might want to remove all menus as normal in the close event then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage that
its
close event only fires when Excel really is about to quit and won't be
cancelled. There are various ways to get the CAI to call a routine in the
wb
to run close code 'only' if Excel and hence the file is about to close. I
believe Chip Pearson has made available on his site a CAI to do that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is probably a
boolean
value telling me the user canceled a close.
--
TomThumb

How can I detect that the user canceled a close?
 

TomThumb,

Good!

It looks like you were trying to use application-level events before. Those
require extra code - a class module among other things - and are not
necessary if you are just trapping events for the workbook the code is in.

Doug
"TomThumb" wrote in message
...
Thanks, Doug, the following code did what I wanted:

Private Sub Workbook_Deactivate()
CreateToolBar
Application.CommandBars(Version).Delete
End Sub

--
TomThumb


"Doug Glancy" wrote:

TomThumb,

Instead of the Open and BeforeClose events, look at using Activate and
Deactivate instead. When a workbook closes the Deactivate event fires
after
the BeforeClose and the Cancel message. Also, then your toolbar will be
deleted when you switch to another workbook, which I'm guessing is also
what
you want.

hth,

Doug

"TomThumb" wrote in message
...
Peter T:

Thank you for responding to my plea for help. I should have spelled
out
what I have in mind:

I have an Add-In Toolbar that I delete in my WorkBookBeforeClose event
procedure. But when the user closes and then clicks "Cancel" when
asked
to
Save, the Close is terminated and the toolbar is gone. I want to
restore
that toolbar, or not delete it in the first place.

Can I somehow get a boolean from WorkbookBeforeSave?
--
TomThumb


"Peter T" wrote:

"user canceled a close" - do you mean if user cancels Excel-quit or
merely
while doing file-close, that might be relevant depending on your
ultimate
objective. However in either case there is no direct method to return
your
boolean if user cancelled close during the 'save unsaved file(s)
prompt'.

I assume you want to know so as to determine whether or not to run
your
close event code. One workaround is to run the close event as normal,
but
also call a routine with the OnTime method to restore as-was. Eg with
an
addins, might want to remove all menus as normal in the close event
then
put
them back again a few seconds later if the workbook is still open.

A different approach is to use a Com-addin. This has the advantage
that
its
close event only fires when Excel really is about to quit and won't be
cancelled. There are various ways to get the CAI to call a routine in
the
wb
to run close code 'only' if Excel and hence the file is about to
close. I
believe Chip Pearson has made available on his site a CAI to do that,
which
makes use of the 'hidden namespace'. I have a CAI that does similar
but
with
a different approach (doesn't use the namespace) if interested.

Regards,
Peter T

"TomThumb" wrote in message
...
That is all I need to know: Just how can I obtain what is probably
a
boolean
value telling me the user canceled a close.
--
TomThumb