If a menu component applies only to a particular sheet or workbook, it's best if that menu component appears only when that sheet or workbook is active; this reduces needless clutter in the interface. However, unlike changes made to the menu system with the Menu Editor, changes made using Visual Basic are stored globally, and their effects cannot be limited to a given workbook or sheet. You can, however, limit their lifetime to the period between the opening and subsequent closing of a particular workbook or the period between the activation and subsequent deactivation of a given sheet.
If you want a menu or menu item to appear only on a specific sheet, Visual Basic must add the component to the menu system every time the user activates the sheet and delete it every time the user deactivates the sheet. If you want to associate a menu bar with a sheet, Visual Basic can activate the menu bar when the user activates the sheet and then deactivate the menu bar when the user deactivates the sheet, rather than add the entire menu bar and then delete it. To do this, set the sheet's OnSheetActivate property to a procedure that adds or activates the component, and set the sheet's OnSheetDeactivate property to a procedure that removes or deactivates the component.
The following example causes the procedure AddNewMenu to run whenever the worksheet named "data108" is activated. This procedure adds a new menu to the Worksheet menu bar. The procedure RemoveNewMenu removes the menu when the worksheet is deactivated.
With Worksheets("data108")
.OnSheetActivate = "AddNewMenu"
.OnSheetDeactivate = "RemoveNewMenu"
End With
Use the Auto_Open and Auto_Close procedures attached to a workbook to add and remove menu components or to activate and deactivate a menu bar whenever that workbook is opened and closed. If you close the workbook without removing any new menus that you created while it was open, these menus will remain on the menu bar until you quit Microsoft Excel.