In Microsoft Excel, you can add, delete, and modify menu components using either the Menu Editor or Visual Basic. Although the two methods produce many of the same changes to the menu system, the scope of the changes they produce is different: Visual Basic produces changes that apply to all workbooks during a specific Microsoft Excel session, whereas the Menu Editor produces changes that apply to all Microsoft Excel sessions whenever a specific workbook is open. Defining the appropriate scope for the changes you want to make will help you decide which method to use.
Changes you make to the menu system using Visual Basic methods and properties apply to all open workbooks until the end of the Microsoft Excel session during which the changes are made, even if you close the workbook where you've made the changes. If you want to limit the scope of changes made by Visual Basic to a specific sheet, set the sheet's OnSheetActivate property to the procedure that makes these changes, and set the sheet's OnSheetDeactivate property to the procedure that undoes these changes.
When you close Microsoft Excel, any changes you made to the menu system using Visual Basic are lost. If you want the menu changes to be available when you restart Microsoft Excel and reopen the workbook, you must use Visual Basic to reset the menus to the desired configuration when you reopen the workbook. An easy way to do this is to use an Auto_Open procedure. Because the changes you make to the menu system with Visual Basic are stored globally for the duration of a session, rather than in a particular workbook, these changes aren't shown in the Menu Editor.
Changes you make to the menu system using the Menu Editor — including adding or deleting menu items — are stored as a menu editing list in the active workbook. The changes aren't lost when you close Microsoft Excel, and they're automatically reapplied to the menu system when you reopen the workbook.
For example, suppose you've only one workbook open: You edit the menu system in the workbook using the Menu Editor, and you develop procedures that support the menus, worksheets, and other objects in your application. If you then save and close the workbook, the menu system reverts to its built-in state. When you reopen the workbook, the menu system is revised according to the menu editing list that was stored when the workbook was last saved.
Changes you make to the menu editing list for a workbook are reflected in the menu system whenever that workbook is open, but the Menu Editor shows only the changes made in the active workbook, no matter how many workbooks are open. If multiple workbooks containing menu editing lists are open, the menu system will combine and display the changes in all the workbooks, but the Menu Editor will still show only the changes made in the active workbook.
Note
Because the Menu Editor doesn't show changes made with Visual Basic or changes made by the Menu Editor in other workbooks, the menu components shown in Microsoft Excel may not agree with the menu components shown in the Menu Editor.
For example, suppose you make some changes to the built-in File menu on the worksheet menu bar, where the first three menu items are New, Open, and Close. First, you open a new workbook, name it Mybook1.xls, and add an Open Special menu item to the File menu, just above the Close item. Then you close Mybook1.xls, open a second workbook, name this new workbook Mybook2.xls, and add an Open Database menu item just above the Close item on the File menu. If you then reopen Mybook1.xls (with Mybook2.xls still open), the File menu contains all the menu items: New, Open, Open Database, Open, and Close. (Because the workbook whose menu editing list contains Open Special is the most recently opened workbook, Open Special appears below Open Database on the menu.)
However, if you start the Menu Editor while Mybook1.xls is the active workbook, select the worksheet menu bar, and then open the File menu, you see only the New, Open, Open Special, and Close menu items. If you bring up the Menu Editor while Mybook2.xls is active, you see New, Open, Open Database, and Close on the File menu. The Menu Editor displays the effects of only the active workbook's menu editing list.