Programming Excel with VBA and .NET

Add a Menu Item

To add a menu item for an add-in, follow these general steps:

1. Get a reference to the Tools pop-up menu on the worksheet’s menu bar.

2. Find the location in that menu where you want to add the item. Usually, I add menu items just before the last separator bar (the next-to-last group).

3. Create a new command bar button control and add it to the menu.

4. Set the control’s Tag, OnAction, and Caption properties.

5. Optionally repeat the task for the Chart menu bar.

The following code adds a menu item that displays the CodeToolbar created in Chapter 19; I’ve also included the code to remove the menu item since I’ll use that next:

 ' Menu code module. Sub AddMenuItem(Optional cb As String = "Worksheet Menu Bar") Dim cpop As CommandBarPopup, cbc As CommandBarControl, _ loc As Integer ' Get the Tools menu. Set cpop = Application.CommandBars(cb).FindControl(, 30007) ' Find the last separator bar. For Each cbc In cpop.Controls If cbc.BeginGroup Then loc = cbc.index Next ' Insert the menu item before the last separator bar. Set cbc = cpop.Controls.Add(msoControlButton, , , loc, False) cbc.Caption = "&CodeToolbar" cbc.Tag = "mnuCodeToolbar" cbc.OnAction = "mnuCodeToolbar_Click" End Sub ' Procedure for menu item's OnAction property. Sub mnuCodeToolbar_Click( ) Dim cbc As CommandBarButton ' Get the menu item Set cbc = Application.CommandBars("Worksheet Menu Bar").FindControl( _ , , "mnuCodeToolbar", , True) ' Exit if menu item not found. If cbc Is Nothing Then Exit Sub ' Toggle the state (adds ...