Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Menu Handling
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. ToolsAddIns 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Menu Handling
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 "Stephen Lloyd" wrote in message ... 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. ToolsAddIns 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Menu Handling
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. Stephen Lloyd wrote: 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. ToolsAddIns 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add-In Menu Handling
On Nov 4, 8:21*am, Dave Peterson wrote:
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. Stephen Lloyd wrote: 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. ToolsAddIns 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop dwn menu. Formula to count selection frm menu in anoth cell? | Excel Worksheet Functions | |||
Custom Context menu (Right click menu) not working in sheet changeevent. | Excel Programming | |||
Error Handling for "my" menu bar | Excel Discussion (Misc queries) | |||
Error handling with a handling routine | Excel Programming | |||
Menu items added with menu item editor in older versions | Excel Discussion (Misc queries) |