Fixing a menu that has been reset

Consider this scenario: You write VBA code that creates a new menu when your workbook application is opened. The user opens another workbook containing a macro that resets Excel's menu bar. Or consider this: The user plays around with the Customize dialog box, selects the Workbook Menu Bar from the list of that dialog box, and then clicks the Reset button. In both cases, your custom menu is destroyed.

Your menu-making code is probably triggered by the Workbook_Open event, so the only way that the user can get your menu back is to close and reopen the workbook. To provide another way, create a key combination that executes the procedure that builds your menu.

Apparently, applications that reset Excel's menu bar are not uncommon. Users of my Power Utility Pak add-in sometimes tell me that the PUP v5 menu has disappeared for no apparent reason. This is always caused by some other application that feels it must reset the Worksheet Menu Bar. Therefore, I added a key combination (Ctrl+Shift+U) that when pressed, rebuilds the PUP v5 menu. The following statement, when executed, associates the CreateMenu procedure with the Ctrl+Shift+U key combination.

Application.MacroOptions _Macro:="CreateMenu", _ HasShortcutKey:=True, ShortcutKey:="U"

0 0

Post a comment