P
Peter Benson
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....")?
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....")?