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

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






.

Hi , Peter

Thanks for posting in the group.

I have reviewed this thread. Currently I am finding somebody who could help
you on it. We will post back in the newsgroup as soon as possible.

If there is anything unclear, please feel free to post in the group and we
will follow up there.

Thank you for using Microsoft Newsgroup!

Wei-Dong XU
Microsoft Developer Support

Hi Peter,

As I understand your question, you have written a COM Add-in for Microsoft
Excel. The add-in creates a custom menu in Microsoft Excel and you would
like to find a method to remove the custom menu when the user exits
Microsoft Excel to prevent it from being saved within the Excel.xlb file.

You have tried various application events within Microsoft Excel but
haven't found an event that would provide an optimal solution.

Have you tried adding the code to remove the custom menu to either the
OnDisconnection or OnBeginShutdown events of the COM Add-in?

If not, I would suggest using one of these events as they should only be
executed when the COM Add-in is being unloaded or when the Excel
Application begins to shut down which I believe would provide a cleaner
solution to 1) prevent the custom menu from being added to the XLB file and
2) to insure that the menu is unloaded when the user is exiting Microsoft
Excel.

I hope this helps!

If you have any questions please let me know via the posting.

Regards,

Ken Laws
Microsoft Support


This posting is provided "AS IS" with no warranties, and confers no rights.

Ken -- the add-in I have written is an Excel add-in, not a COM add-in. ie
it's written in Excel VBA. As such I'm not aware of how I would get access
to the OnDisconnection or OnBeginShutdown events of the add-in. If you see
my original post I describe two of the events I trap (app_WorkbookDeactivate
and app_WorkbookBeforeClose ), neither of which provides a clean solution
that works in all cases.

If there is a way to use the two events you describe then could you please
let me know how I can do this?

Thanks,

Peter

"Ken Laws [MSFT]" wrote in message
...

Hi Peter,

As I understand your question, you have written a COM Add-in for Microsoft
Excel. The add-in creates a custom menu in Microsoft Excel and you would
like to find a method to remove the custom menu when the user exits
Microsoft Excel to prevent it from being saved within the Excel.xlb file.

You have tried various application events within Microsoft Excel but
haven't found an event that would provide an optimal solution.

Have you tried adding the code to remove the custom menu to either the
OnDisconnection or OnBeginShutdown events of the COM Add-in?

If not, I would suggest using one of these events as they should only be
executed when the COM Add-in is being unloaded or when the Excel
Application begins to shut down which I believe would provide a cleaner
solution to 1) prevent the custom menu from being added to the XLB file
and
2) to insure that the menu is unloaded when the user is exiting Microsoft
Excel.

I hope this helps!

If you have any questions please let me know via the posting.

Regards,

Ken Laws
Microsoft Support


This posting is provided "AS IS" with no warranties, and confers no
rights.

Hi Peter,

An Excel add-in (XLA) does not have an equivalent event to the
OnDisconnection or OnBeginShutdown events for a COM Add-in.

As such I believe that the best solution in this situation would be to add
your own custom code to the WorkBook_BeforeClose event that would check for
any workbooks that haven't been saved, and then display a custom message
box that would contain the Yes, No, and Cancel options as the dialog
displayed by Excel.

This would allow the code in your add-in to handle a scenario where the
user would click Cancel so that the menus would not be removed.

I have included sample code below to demonstrate what this code might look
like. Please note that the code below is not complete and you would need
to customize this for your add-in.

Sample Code
=====================
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wbk As Workbook


For Each wbk In Application.Workbooks

If wbk.Saved = False Then

CloseWorkbooks wbk, Cancel

End If

Next

End Sub


Sub CloseWorkbooks(wbk As Workbook, ByRef Cancel As Boolean)

Select Case MsgBox("Do you want to save the changes you made to '" &
wbk.Name & "'?", _
vbExclamation + vbYesNoCancel + vbDefaultButton1, "Microsoft Excel")

Case vbYes

If wbk.Path < "" Then

wbk.Save

Else

wbk.Activate

If Application.Dialogs(xlDialogSaveAs).Show = False Then

CloseWorkbooks wbk, Cancel

Else

Exit Sub

End If

End If

Case vbNo

wbk.Saved = True

Case vbCancel

Cancel = True

Exit Sub

End Select

End Sub
====================================

I hope this helps!

If you have any questions please let me know via the posting.

Regards,

Ken Laws
Microsoft Support


This posting is provided "AS IS" with no warranties, and confers no rights.