You should always create two menu procedures. One menu procedure creates the menu bar. This can be called at Workbook_Open, Workbook_Activate, or Worksheet_Activate. You should also have a DeleteToolbar procedure that is called at Workbook_BeforeClose.

The CreateToolbar macro should call DeleteToolbar first to remove any stray copy of the menu that might still exist.

The following code shows the basic structure of the CreateToolbar and DeleteToolbar macros. The object variable MenuObject is defined as a CommandBarPopup. The code adds a control to the main Excel Menu bar, places it before the Help menu item, and gives it a label of XYZ Co:

Sub DeleteMenu()

'This sub should be executed when the workbook is closed On Error Resume Next

Application.CommandBars(1).Controls("&XYZ Co").Delete On Error GoTo 0 End Sub

Sub CreateMenu()

Dim MenuObject As CommandBarPopup Dim Menultem As Object

' Make sure the menus aren't duplicated Call DeleteMenu

Set MenuObject = Application.CommandBars(l). _ Controls.Add(Type:=msoControlPopup, _ Before:=10, temporary:=True) MenuObject.Caption = "&XYZ Co"

Set Menultem = MenuObject.Controls.Add(Type:=msoControlButton) Menultem.OnAction = "ImportData" Menultem.Caption = "&Import Data" End Sub

0 0

Post a comment