Option Explicit 
 
 'This section of code goes in ThisWorkbook.  When the addin is
 'installed, it runs the procedure to add the desired menu and
 'menu items to the toolbar.  This example adds a new menu before the
 'Excel Help menu in the worksheet menu bar.  To see how to install
 'the addin when it is complete, see comments at the bottom of this code
 'For a list of control ID's, visit
 'http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q213552
 
 
Private Sub Workbook_AddinInstall() 
     
    Dim ctrlMain As CommandBarPopup 'this is what is seen next to "Help"
    Dim ctrlItem As CommandBarControl 'this is what is seen when the new menu is clicked on
    Dim iHelpIndex As Long 'this will be set to the position of "Help"
    Dim cbHelp As CommandBarControl 'used to find existing "Help" item
     
     'This finds the position of the Help menu; returns an integer
    Set cbHelp = Application.CommandBars(1).FindControl(ID:=30010) 
    iHelpIndex = cbHelp.Index 
     
     'each time Excel is started, we need to remove and rebuild the
     'menu so that the user doesnt end up with a bunch of them.
     
    KillMenu 
     
     'this next section builds the menu itself in front of the Help menu
    Set ctrlMain = Application.CommandBars(1).Controls.Add _ 
    (Type:=msoControlPopup, Before:=iHelpIndex, temporary:=False) 
     
    With ctrlMain 
         'having the "&" allows alt key combos to work
        .Caption = "&My New Menu" 
         
         'for each desired menu item, add a control.  There are two here.
        Set ctrlItem = .Controls.Add(Type:=msoControlButton) 
        With ctrlItem 
            .Caption = "&Item #1" 
             
             'the OnAction command defines the sub in this workbook to run
            .OnAction = "ThisWorkbook.doItem1" 
        End With 
         
        Set ctrlItem = _ 
        .Controls.Add(Type:=msoControlButton) 
        With ctrlItem 
            .Caption = "&Item #2" 
            .OnAction = "ThisWorkbook.doItem2" 
        End With 
    End With 
End Sub 
 
 'We also want to make sure our menu disappears when the user
 'uninstalls the addin from the addins dialog box
 
Private Sub Workbook_AddinUninstall() 
     'this removes the menu itself
    KillMenu 
     
     'this makes sure that the addin is uninstalled.
    Dim xlaName As String 
    Dim i As Integer 
     
     'this loops through all addins and removes this one
    With ThisWorkbook 
        For i = 1 To AddIns.Count 
            xlaName = AddIns(i).Name 
             
            If xlaName = "sampleMenu.xla" Then 
                AddIns(i).Installed = False 
            End If 
        Next i 
    End With 
End Sub 
 
 'this removes the menu from the menu bar
Sub KillMenu() 
    Dim cmdBar As CommandBar 
    Set cmdBar = Application.CommandBars(1) 
     'this turns off error checking and deletes the menu item
    On Error Resume Next 
    Set cmdBar = Application.CommandBars(1) 
    cmdBar.Controls("&My New Menu").Delete 
    On Error GoTo 0 'turns error checking back on
End Sub 
 
 'each sub here runs when the user clicks on the appropriate menu item
 
Sub doItem1() 
     'here you can call dialog boxes or insert code for the action to
     'take when the user clicks on the menu item
     'Here we will just show a message box
    MsgBox ("Thanks for clicking on Item #1!") 
End Sub 
 
Sub doItem2() 
    MsgBox ("Thanks for clicking on Item #2!") 
End Sub 
 
 'NOW that you have all of this code in ThisWorkbook, it is time to
 'save it as an xla.  To do this, go to the Excel menu (not the VBE menu)
 'and select File > Save As.  In the Save As dialog box, you should find
 '"Microsoft Office Excel Add In (*.xla)" at the very bottom.  Give your
 'xla a name, note the location and click Save.  NOW, go to the tools
 'menu in Excel, select "Add Ins" from the tools menu, click on
 'Browse (Select on Mac), and navigate to the location where you saved
 'the xla file.  Highlight the xla, click OK twice, and you should see
 'the new menu appear!