Adding Menus To The VBA Editor

Adding Menu Items To The VBA Editor

This page has been replaced. See Creating Menus In The VBA Ediitor.

If you do a lot of VBA coding, you may have wanted to customize the menus in the VBA Editor (VBE). Unfortunately, this is not as simple as it is with standard Office command bars. In the VBE, you cannot simply drag around menu items and assign macros. This is because the VBE is based on the Visual Basic (Visual Studio) model, not the Office model. Therefore, you have to create and handle your custom menus through code.

For information about working with menus in Excel, click here. For general information about programming the VBA Editor, see the Programming To The VBE page.

This page describes the code required to add two simple menu items to the Tools menu of the VBE. These procedures work for Excel97 and Excel2000. They will not work in earlier versions of Excel. Because the VBA procedures here use the CommandBar and CommandBarControl object types, you must set a reference in your VBA Project to the Microsoft Office 8 (or 9) Object Library.

There are two steps in customizing the VBE menus. First, you have to create new menu items and assign them to a menu and give them captions. This is the same as you would do to create menu items on the standard Excel menus. The second step is to associate each item to an instance of a class module that will actually handle the click event for the menu item.

You'll need a reference to the "Microsoft Visual Basic For Applications Extensibility" module to use the code. From the Tools menu, choose References, and select this entry from the list.

First create a class module called VBECmdHandler. From the Insert menu, choose Class Module. Select this module in the Project Window, and press F4 to view the Property dialog box. Change the Name property from Class1 to VBECmdHandler.

In this class module, enter the following code:

Public WithEvents EvtHandler As VBIDE.CommandBarEvents

Private Sub EvtHandler_Click(ByVal CommandBarControl As Object,_
Handled As Boolean, CancelDefault As Boolean)

On Error Resume Next

'
' Run the code specified in the object's OnAction property.
'
Application.Run CommandBarControl.OnAction

'
' Indicate to the Events object that we've successfully handled the event.
'
Handled = True
CancelDefault = True
End Sub

This is all the code that needs to be in the module. This code declares an object called EvtHandler of the type CommandBarEvents. Since this object is declared with the WithEvents keyword, event triggers are passed to it by the VBE. This is the object that acts as the "bridge" between your code in this module and the menu items you'll add. As you'll see later, when you create the menu item, you point the VBE to this object as the "recipient" of the menu click events.

The EvtHandler_Click event, the only event there is for the EvtHandler object, gets executed when the menu item is clicked. As you'll see later, we're using one class module with on EvtHandler to manage all of the menu items we're going to add. Therefore, the code looks at the OnAction property of the CommandBarControl object to determine what procedure to run, which is called with Application.Run. Unlike standard Excel command bar controls, the OnAction property of controls on VBE command bars does not actually cause the code to be execute. We use it to store the procedure name which we want to execute, but our code has to manually call that procedure.

Next, we need to add the code to run create the menu items, and to create the link between the menu item and the class module. Create a standard code module, and enter in the following global declarations:

Dim MnuEvt As VBECmdHandler
Dim CmdItem As CommandBarControl
Dim EvtHandlers As New Collection

The VBECmdHandler must be the same name as the class module that you created earlier. This statement creates an object called MnuEvt which is defined by the class VBECmdHandler. The CmdItem object is a standard CommandBarControl object, and EvtHandlers is a collection where we'll store each that we create. Since we're using the same MnuEvt object for each menu item we create, we need to store copies of each instance of that object so that each menu item is handled separately.

The next step is to create the procedure to add the menu items. This is shown below.

Sub AddNewMenuItems()

While EvtHandlers.Count > 0
    EvtHandlers.Remove 1
Wend

With Application.VBE.CommandBars("Menu Bar").Controls("Tools")
    .Reset
    Set CmdItem = .Controls.Add
    CmdItem.Caption = "New Item 1"
    CmdItem.BeginGroup = True
    CmdItem.OnAction = "'" & ThisWorkbook.Name & "'" & "!Macro_One"

    Set MnuEvt = New VBECmdHandler

    Set MnuEvt.EvtHandler = Application.VBE.Events.CommandBarEvents(CmdItem)
    EvtHandlers.Add MnuEvt

'----------------------------------------------------------------
' Now, add the second menu item to the "Tools" menu.
'
    Set CmdItem = .Controls.Add
    CmdItem.Caption = "New Item 2"
    CmdItem.OnAction = "'" & ThisWorkbook.Name & "'" & "!Macro_Two"

    Set MnuEvt = New VBECmdHandler
    Set MnuEvt.EvtHandler = Application.VBE.Events.CommandBarEvents(CmdItem)

    EvtHandlers.Add MnuEvt
End With

End Sub

This procedure adds two new items to the Tools menu. The first steps are the same as adding a menu item to the standard Excel menus. The code

Set MnuEvt = New VBECmdHandler

sets the MnuEvt object to a new instance of the VBECmdHandler class. The next line

Set MnuEvt.EvtHandler = Application.VBE.Events.CommandBarEvents(CmdItem)

sets the EvtHandler property to the CommandBarEvents object of the VBE for the CmdItem menu item. The EvtHandler is the object we declared WithEvents in the class module. These two lines of code create the "bridge" between the menu item CmdItem and the code that will execute when the item is clicked.

Since we're going to be adding another menu item and event using the same MnuEvt object, we need to save a copy of the current object so that it will not be overwritten. We do this by adding it to the EvtHandlers collection. By doing this, you don't have to declare a separate VBECmdHandler object for each menu item. This makes the code much easier to maintain, especially when you're adding several menu items in a loop.

To test this code, create two new macros, Macro_One and Macro_Two, which will be executed when you click the menu items. For example,

Sub Macro_One()
    Debug.Print "Macro One"
End Sub
Sub Macro_Two()
    Debug.Print "Macro Two"
End Sub

Since all of the objects that are created in these procedures are within the workbook which created them, the will exist (and therefore your menus will work) only when this workbook is open. To have these menu items functional regardless of what workbook is open, you may want to put all of these modules and code in an Add-In module that will be loaded whenever Excel is running.

You can download this a workbook which illustrates this code by clicking here (15KB zip file) .

If you'd like to learn more about working with the VBE command bars, I would very highly recommend the book Excel 2000 VBA Programmer's Reference, written by John Green with Stephen Bullen, both Microsoft MVPs well known the regular readers of the Excel newsgroups. Stephen Bullen has on his web site two add-in modules available for download which go into much more detail than this page and the example file I provide. For Excel2000, download VBETools2000 (about 234 KB self-extracting file). For Excel97, download VBETools (about 29 KB zip file). I would also recommend you spend some time learning about the other downloadable files that Stephen has made available on his site.