Add-In Menu Handling

[Stephen Lloyd]

I'm using Excel 2003. I have an add-in that creates a menu in the
workbook open event and uses a class module to handle application
level events. Call that xlApp.

In the xlApp_WorkbookBeforeClose() event I call code to remove the
menu. This works fine if the user is removing the add-in in the
traditional sense, i.e. Tools>AddIns etc. There is, however, an
undesired behavior when a user is attempting to close a workbook and
mistakenly clicks the close icon for the Excel Application.

If all workbooks were saved when the user misclicked, then tough luck,
restart excel. If, however, there were unsaved changes in one or more
workbooks the user is graced with an opportunity to catch their
error. A dialog box appears with options Yes, [Yes to all], No, and
Cancel. If the user selects cancel, then none of the workbooks will
close.

The problem is that the BeforeClose() events for one or more workbooks
have already fired and the add-ins menu was removed.

So, I'm looking for a way to detect this situation and either avoid
deleting the menu or add the menu again.

In what order does Excel run the BeforeClose() events? ( I assume in
the index order of the workbooks)
Application level events are fairly limited and consist mostly of
events for child objects, however, as I write this I'm wondering if
something in the WindowActivate event might work.

Any one have a solution to this problem

 

[Peter T]

There's no direct way to trap an aborted close after the CloseEvent has run.
At least not in VBA although there are various approaches with the help of a
ComAddin to call back to the addin to run close code (it's close event runs
when close cannot be aborted).

A simple workaround is in the close event to include an OnTime macro to
reapply the removed menus later if the addin still exists, say 8 seconds is
generally enough. You also need to set a flag in the AddinUninstall event
to prevent that OnTime macro.

Regards,
Peter T

 

[Dave Peterson]

Another option would be to give the routine that builds the toolbar/menu a
shortcut key.

Then tell the user that if they don't see the toolbar/menu to hit the shortcut
key.

I'm using Excel 2003. I have an add-in that creates a menu in the
workbook open event and uses a class module to handle application
level events. Call that xlApp.

In the xlApp_WorkbookBeforeClose() event I call code to remove the
menu. This works fine if the user is removing the add-in in the
traditional sense, i.e. Tools>AddIns etc. There is, however, an
undesired behavior when a user is attempting to close a workbook and
mistakenly clicks the close icon for the Excel Application.

If all workbooks were saved when the user misclicked, then tough luck,
restart excel. If, however, there were unsaved changes in one or more
workbooks the user is graced with an opportunity to catch their
error. A dialog box appears with options Yes, [Yes to all], No, and
Cancel. If the user selects cancel, then none of the workbooks will
close.

The problem is that the BeforeClose() events for one or more workbooks
have already fired and the add-ins menu was removed.

So, I'm looking for a way to detect this situation and either avoid
deleting the menu or add the menu again.

In what order does Excel run the BeforeClose() events? ( I assume in
the index order of the workbooks)
Application level events are fairly limited and consist mostly of
events for child objects, however, as I write this I'm wondering if
something in the WindowActivate event might work.

Any one have a solution to this problem

 

[Stephen Lloyd]

Another option would be to give the routine that builds the toolbar/menu a
shortcut key.

Then tell the user that if they don't see the toolbar/menu to hit the shortcut
key.

I'm using Excel 2003.  I have an add-in that creates a menu in the
workbook open event and uses a class module to handle application
level events.  Call that xlApp.

In the xlApp_WorkbookBeforeClose() event I call code to remove the
menu.  This works fine if the user is removing the add-in in the
traditional sense, i.e. Tools>AddIns etc.  There is, however, an
undesired behavior when a user is attempting to close a workbook and
mistakenly clicks the close icon for the Excel Application.

If all workbooks were saved when the user misclicked, then tough luck,
restart excel.  If, however, there were unsaved changes in one or more
workbooks the user is graced with an opportunity to catch their
error.  A dialog box appears with options Yes, [Yes to all], No, and
Cancel.  If the user selects cancel, then none of the workbooks will
close.

The problem is that the BeforeClose() events for one or more workbooks
have already fired and the add-ins menu was removed.

So, I'm looking for a way to detect this situation and either avoid
deleting the menu or add the menu again.

In what order does Excel run the BeforeClose() events?  ( I assume in
the index order of the workbooks)
Application level events are fairly limited and consist mostly of
events for child objects, however, as I write this I'm wondering if
something in the WindowActivate event might work.

Any one have a solution to this problem

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for the ideas gentleman. I'll post back when I've worked one
(or both out).