question about Auto_Close and WorkbookBeforeClose
 

When an addin of mine closes I have the Auto_Close in the addin remove a custom menu.

If the user gives the File/Exit command in Excel, the menu is removed before any dialog appears about saving open files. If he then cancels the close action, excel stays open but my menu is gone.

Is there a way to prevent this?

Thanks,

Brian Murphy
Austin, TX

question about Auto_Close and WorkbookBeforeClose
 

I don't know of a good way around this.
What I've done in the past is catch the Workbook_BeforeClose event and
present a "Save As" dialog to the user in exactly the same way Excel does.

I have an example on my website "Save Changes?"

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Brian Murphy" wrote in message
...
When an addin of mine closes I have the Auto_Close in the addin remove a
custom menu.

If the user gives the File/Exit command in Excel, the menu is removed before
any dialog appears about saving open files. If he then cancels the close
action, excel stays open but my menu is gone.

Is there a way to prevent this?

Thanks,

Brian Murphy
Austin, TX

question about Auto_Close and WorkbookBeforeClose
 

Hi Brian,

Unfortuantely, there isn't much you can do about this. When the user
closes Excel, all auto-close procedures in all add-ins run first, followed
by any open user workbooks. By the time a user is prompted to
save/close/cancel an open workbook, your add-in's Auto_Close or
Workbook_BeforeClose macro has already run.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Brian Murphy" wrote in message
...
When an addin of mine closes I have the Auto_Close in the addin remove a
custom menu.

If the user gives the File/Exit command in Excel, the menu is removed before
any dialog appears about saving open files. If he then cancels the close
action, excel stays open but my menu is gone.

Is there a way to prevent this?

Thanks,

Brian Murphy
Austin, TX

question about Auto_Close and WorkbookBeforeClose
 

Thank you, Rob & Rob, for your replies.

It looks like the only recourse would be to take over Excel's job of
prompting to save as needed for all open files. I'm not too keen on that
idea because what works now may not work in the future. I sense there's too
many booby-traps lurking unseen.

But it's still a problem when the user suddenly sees that my menu is gone.
My app also has a custom toolbar which is not deleted, but it is made
invisible at the same time the menu is removed. I have the menu regenerated
if any toolbar button is clicked, but the user has to make the toolbar
visible again.

My app is only used with special workbooks. One possibility I'll think
about is to give all those workbooks a workbook_activate event that makes
sure the toolbar and menu are visible. But there obviously are logistical
problems with that approach (updating existing customer files with the event
macro).

Sigh!

Brian


"Rob Bovey" wrote in message
...
Hi Brian,

Unfortuantely, there isn't much you can do about this. When the user
closes Excel, all auto-close procedures in all add-ins run first, followed
by any open user workbooks. By the time a user is prompted to
save/close/cancel an open workbook, your add-in's Auto_Close or
Workbook_BeforeClose macro has already run.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Brian Murphy" wrote in message
...
When an addin of mine closes I have the Auto_Close in the addin remove a
custom menu.

If the user gives the File/Exit command in Excel, the menu is removed
before
any dialog appears about saving open files. If he then cancels the close
action, excel stays open but my menu is gone.

Is there a way to prevent this?

Thanks,

Brian Murphy
Austin, TX

question about Auto_Close and WorkbookBeforeClose
 

I have "search/replace code" examples on my website.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Brian Murphy" wrote in message
...
Thank you, Rob & Rob, for your replies.

It looks like the only recourse would be to take over Excel's job of
prompting to save as needed for all open files. I'm not too keen on that
idea because what works now may not work in the future. I sense there's
too
many booby-traps lurking unseen.

But it's still a problem when the user suddenly sees that my menu is gone.
My app also has a custom toolbar which is not deleted, but it is made
invisible at the same time the menu is removed. I have the menu
regenerated
if any toolbar button is clicked, but the user has to make the toolbar
visible again.

My app is only used with special workbooks. One possibility I'll think
about is to give all those workbooks a workbook_activate event that makes
sure the toolbar and menu are visible. But there obviously are logistical
problems with that approach (updating existing customer files with the
event
macro).

Sigh!

Brian


"Rob Bovey" wrote in message
...
Hi Brian,

Unfortuantely, there isn't much you can do about this. When the user
closes Excel, all auto-close procedures in all add-ins run first,
followed
by any open user workbooks. By the time a user is prompted to
save/close/cancel an open workbook, your add-in's Auto_Close or
Workbook_BeforeClose macro has already run.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Brian Murphy" wrote in message
...
When an addin of mine closes I have the Auto_Close in the addin remove a
custom menu.

If the user gives the File/Exit command in Excel, the menu is removed
before
any dialog appears about saving open files. If he then cancels the close
action, excel stays open but my menu is gone.

Is there a way to prevent this?

Thanks,

Brian Murphy
Austin, TX

question about Auto_Close and WorkbookBeforeClose
 

One of the tips in John Walkenbach's Power Programming with VBA (2000 version)
suggested using a shortcut key assigned to the macro that will rebuild your menu
system.

(I used to use ctrl-shift-u.)

But then I saw a post from Jan Karel Pieterse that used application.ontime to
reload the menu system.

If excel closes, then the ontime procedure is null and void.

If excel stays open, then the ontime procedure reloads the menu system.

Here's the post:
http://groups.google.com/groups?thre...40netscape.net





Brian Murphy wrote:

When an addin of mine closes I have the Auto_Close in the addin remove a custom menu.

If the user gives the File/Exit command in Excel, the menu is removed before any dialog appears about saving open files. If he then cancels the close action, excel stays open but my menu is gone.

Is there a way to prevent this?

Thanks,

Brian Murphy
Austin, TX

--

Dave Peterson

question about Auto_Close and WorkbookBeforeClose
 

Hello Rob,

The tip from Dave Peterson about using OnTime seems to work perfectly for me.

At one time I was having my addin do editing of modules in user's workbooks. And about that time (a couple of years ago) I was having Excel crash on me in strange ways, and the crashing stopped when I stopped doing the editing of those modules. I have been reluctant to resume doing that sort of thing. Fortunately, I haven't needed to since.

But, hey, you have some really great stuff on your web site. I was particularly impressed by the one with the rotating sphere built out of polygons. Being able to turn it with the mouse is very cool. I learned something there I hope to use someday.

Hopefully someday Microsoft will get on the ball and give Excel some real 3d graphics capability. The charting features of excel are way behind the times. I'm sure just 1 million of Gates many dollars should be ample to have the charting upgraded, and the investment would be a wise one.

Brian



"Rob van Gelder" wrote in message ...
I have "search/replace code" examples on my website.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Brian Murphy" wrote in message
...
Thank you, Rob & Rob, for your replies.

It looks like the only recourse would be to take over Excel's job of
prompting to save as needed for all open files. I'm not too keen on that
idea because what works now may not work in the future. I sense there's
too
many booby-traps lurking unseen.

But it's still a problem when the user suddenly sees that my menu is gone.
My app also has a custom toolbar which is not deleted, but it is made
invisible at the same time the menu is removed. I have the menu
regenerated
if any toolbar button is clicked, but the user has to make the toolbar
visible again.

My app is only used with special workbooks. One possibility I'll think
about is to give all those workbooks a workbook_activate event that makes
sure the toolbar and menu are visible. But there obviously are logistical
problems with that approach (updating existing customer files with the
event
macro).

Sigh!

Brian


"Rob Bovey" wrote in message
...
Hi Brian,

Unfortuantely, there isn't much you can do about this. When the user
closes Excel, all auto-close procedures in all add-ins run first,
followed
by any open user workbooks. By the time a user is prompted to
save/close/cancel an open workbook, your add-in's Auto_Close or
Workbook_BeforeClose macro has already run.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Brian Murphy" wrote in message
...
When an addin of mine closes I have the Auto_Close in the addin remove a
custom menu.

If the user gives the File/Exit command in Excel, the menu is removed
before
any dialog appears about saving open files. If he then cancels the close
action, excel stays open but my menu is gone.

Is there a way to prevent this?

Thanks,

Brian Murphy
Austin, TX

question about Auto_Close and WorkbookBeforeClose
 

Darn, I have that book, too.

That's absolutely, positively, for sure, a great tip. I've implemented it, and it works beautifully.

Many many thanks, Dave.

This newsgroup is the greatest.

Brian




"Dave Peterson" wrote in message ...
One of the tips in John Walkenbach's Power Programming with VBA (2000 version)
suggested using a shortcut key assigned to the macro that will rebuild your menu
system.

(I used to use ctrl-shift-u.)

But then I saw a post from Jan Karel Pieterse that used application.ontime to
reload the menu system.

If excel closes, then the ontime procedure is null and void.

If excel stays open, then the ontime procedure reloads the menu system.

Here's the post:
http://groups.google.com/groups?thre...etscape..ne t





Brian Murphy wrote:

When an addin of mine closes I have the Auto_Close in the addin remove a custom menu.

If the user gives the File/Exit command in Excel, the menu is removed before any dialog appears about saving open files. If he then cancels the close action, excel stays open but my menu is gone.

Is there a way to prevent this?

Thanks,

Brian Murphy
Austin, TX

--

Dave Peterson

question about Auto_Close and WorkbookBeforeClose
 

Yes, self-modifying code is a dangerous activity. I'd never use it in
production, but rather as a migration tool if I had to convert a lot of
code. I tend to put common code in an Add-In for referencing.

Glad you like the rotating polygon. That was more fun than serious.

Excel does have some powerful charting features.
Just do a search for 'Jon Peltier' or 'Andy Pope' for some excellent
charting examples.

Cheers

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Brian Murphy" wrote in message
...
Hello Rob,

The tip from Dave Peterson about using OnTime seems to work perfectly for
me.

At one time I was having my addin do editing of modules in user's workbooks.
And about that time (a couple of years ago) I was having Excel crash on me
in strange ways, and the crashing stopped when I stopped doing the editing
of those modules. I have been reluctant to resume doing that sort of thing.
Fortunately, I haven't needed to since.

But, hey, you have some really great stuff on your web site. I was
particularly impressed by the one with the rotating sphere built out of
polygons. Being able to turn it with the mouse is very cool. I learned
something there I hope to use someday.

Hopefully someday Microsoft will get on the ball and give Excel some real 3d
graphics capability. The charting features of excel are way behind the
times. I'm sure just 1 million of Gates many dollars should be ample to
have the charting upgraded, and the investment would be a wise one.

Brian



"Rob van Gelder" wrote in message
...
I have "search/replace code" examples on my website.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Brian Murphy" wrote in message
...
Thank you, Rob & Rob, for your replies.

It looks like the only recourse would be to take over Excel's job of
prompting to save as needed for all open files. I'm not too keen on that
idea because what works now may not work in the future. I sense there's
too
many booby-traps lurking unseen.

But it's still a problem when the user suddenly sees that my menu is
gone.
My app also has a custom toolbar which is not deleted, but it is made
invisible at the same time the menu is removed. I have the menu
regenerated
if any toolbar button is clicked, but the user has to make the toolbar
visible again.

My app is only used with special workbooks. One possibility I'll think
about is to give all those workbooks a workbook_activate event that makes
sure the toolbar and menu are visible. But there obviously are
logistical
problems with that approach (updating existing customer files with the
event
macro).

Sigh!

Brian


"Rob Bovey" wrote in message
...
Hi Brian,

Unfortuantely, there isn't much you can do about this. When the user
closes Excel, all auto-close procedures in all add-ins run first,
followed
by any open user workbooks. By the time a user is prompted to
save/close/cancel an open workbook, your add-in's Auto_Close or
Workbook_BeforeClose macro has already run.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Brian Murphy" wrote in message
...
When an addin of mine closes I have the Auto_Close in the addin remove a
custom menu.

If the user gives the File/Exit command in Excel, the menu is removed
before
any dialog appears about saving open files. If he then cancels the
close
action, excel stays open but my menu is gone.

Is there a way to prevent this?

Thanks,

Brian Murphy
Austin, TX