I am fairly new to Excel programming but I believe that
John Walkenbach addresses this issue (or something
similar) on pages 637-638 of his Excel 2000 Power
Programming book (last ed. I have). If you have that
book, check it out. I may be off-base, but thought it may
be worth a shot.

Good luck!

-----Original Message-----
I sell an add-in which creates a custom menu so users can
easily access the
add-ins functions. All the menus and buttoms etc. are
designated as
temporary which means that when Excel exits the details
should be removed
from Excel and NOT stored in the XLB file. In actual
fact this doesn't
work correctly as after exiting Excel if the custom menus
are not explicitly
removed by VBA code the Excel XLB file will grow by about
1.5 K each time.
This seems to affect all versions of Excel.

The obvious solution is to remove the custom menus before
the user exits
Excel. However, this turns out to have no obvious
solution that works in
all situations.

I use an event class module to trap various application
events like workbook
close, open, etc. But there doesn't appear to be a clean
solution using
this technique.

Using app_WorkbookBeforeClose superficially would appear
to work. But this
event is activated BEFORE the save: yes/no/cancel dialog
is displayed to the
user. If the user selects cancel to return to the
workbook the custom menu
has already been removed. This is not acceptable. There
is no event that you
can trap when the user presses cancel so there is no way
to restore the menu
that I know of. WorkbookActivate and other events are
NOT triggered in this
situation.

Using app_WorkbookDeactivate and only removing the menu
when workbooks.count
= 1 works well when there is only one workbook open. If
the user has opened
multiple workbooks and the user exits Excel, the
app_WorkbookDeactivate
event is only invoked ONCE rather than once for every
workkbook. Hence
because workbooks.count is not 1 the menu never get
removed. (If you don't
test for workbooks.count =1 the menu will be removed
every time you switch
to another window so testing for workbooks.count =1 is
essential as you only
want to remove the workbook when the LAST workbook has
been decactivated).

Various other combinations of event monitoring similarly
fail to provide a
solution that works in all situations. The problem of
the XLB file growing
by 1.5K each time the user exits after having opened more
than 1 workbook in
Excel may not sound like much of a problem but active
users over a period of
months can very quickly end up with a bloated XLB file
which significantly
slows down loading and exiting Excel. Telling them to
manually delete their
XLB file every so often is not exactly a clean
professional solution.

Can anybody suggest a solution (that they know works,
rather than "how about
try this....")?






.