ThreeWave Creating Menu Items In The VBA Editor

This page describes how to create menu items in the VBA Editor.
ShortFadeBar

Introduction

Adding menu items the VBA Editor (VBE) menus is somewhat different than adding menus to the main Excel menus. A workbook or add-in that creates and responds to menu items in the VBE needs at least two modules: a code module to contain the code to create the menu items and the procedures to be called by the menu items, and a class module to respond the the Click events of the menu items.

In order to use the code in your projects, you must change two settings.

  • First, you need to set an reference to the VBA Extensibililty library. The library contains the definitions of the objects that make up the VBProject. In VBA, go the the Tools menu and choose References. In that dialog, scroll down to and check the entry for Microsoft Visual Basic For Applications Extensibility 5.3
    • .

  • Next, you need to enable access to the VBA project. In Excel 2003 and earlier, go the Tools menu (in Excel, not in the VBA editor), choose Macros and then the Security item. In that dialog, click on the Trusted Publishers tab and check the Trust access to the Visual Basic Project setting.

    In Excel 2007, click the Developer item on the main Ribbon and then click the Macro Security item in the Code panel. In that dialog, choose Macro Settings and check the Trust access to the VBA project object model.

You can download an example workbook with all the on this page code.

SectionBreak

Creating The Class To Respond To Events

You first need a class that will respond to the Click event of the menu or command bar item. Since the other code in the project will use this class, we'll write the class code first. Insert a new class module in your project. From the Insert menu, choose Class Module. Press the F4 key to display the Properties window and change the Name property from Class1 to CVBECommandHander. 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)
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' This is called when a item is clicked. Call the
    ' procedure named in the OnAction property of the
    ' CommandBarControl passed into this procedure.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    On Error Resume Next
    Application.Run CommandBarControl.OnAction
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Tell VBIDE that we've handled the event.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    Handled = True
    CancelDefault = True
End Sub

The EvtHandeler variable will be set to a CommandBarEvents object provided by the VBE for each new item that we will create. When a menu item is clicked, this procedure is called automatically. The code looks in the OnAction property of the menu item to get the name of the procedure assigned to that menu item and then calls that procedure using the Application.Run method. A new instance of the CVBECommandHandler class is created for each menu item that will be added. These instances will be stored in a Collection object as they are created.

SectionBreak

Creating The Menu Items

The next step is to write the code to create the menu items. This code will do the following for each item that we create:

  • Create a new instance of the CVBECommandHandler class.
  • Add a menu item to the Tools menu.
  • Set the properties of that menu item, such as the Caption and OnAction.
  • Set the EvtHandler of the newly created CVBECommandHandler object to a CommandBarEvents object provided by the VBE.
Create a new code module for the project. From the Insert menu, choose Module. In that module, use the following code:
Private MenuEvent As CVBECommandHandler
Private CmdBarItem As CommandBarControl
Private EventHandlers As New Collection

'''''''''''''''''''''''''''''''''''''''''''''''''
' The C_TAG constant is used to identify controls
' added by this project. You should change the 
' value of this constant to something unique. It
' will be used to delete the controls when the 
' workbook is closed and the project is unloaded.
'''''''''''''''''''''''''''''''''''''''''''''''''
Private Const C_TAG = "MY_VBE_TAG"

Sub AddNewVBEControls()

Dim Ctrl As Office.CommandBarControl

'''''''''''''''''''''''''''''''''''''''''''''''''
' Delete any existing controls with our Tag.
'''''''''''''''''''''''''''''''''''''''''''''''''
Set Ctrl = Application.VBE.CommandBars.FindControl(Tag:=C_TAG)
Do Until Ctrl Is Nothing
    Ctrl.Delete
    Set Ctrl = Application.VBE.CommandBars.FindControl(Tag:=C_TAG)
Loop

'''''''''''''''''''''''''''''''''''''''''''''''''
' Delete any existing event handlers.
'''''''''''''''''''''''''''''''''''''''''''''''''
Do Until EventHandlers.Count = 0
    EventHandlers.Remove 1
Loop

'''''''''''''''''''''''''''''''''''''''''''''''''
' add the first control to the Tools menu.
'''''''''''''''''''''''''''''''''''''''''''''''''
Set MenuEvent = New CVBECommandHandler
With Application.VBE.CommandBars("Menu Bar").Controls("Tools")
    Set CmdBarItem = .Controls.Add
End With
With CmdBarItem
    .Caption = "First Item"
    .BeginGroup = True
    .OnAction = "'" & ThisWorkbook.Name & "'!Procedure_One"
    .Tag = C_TAG
End With

Set MenuEvent.EvtHandler = Application.VBE.Events.CommandBarEvents(CmdBarItem)
EventHandlers.Add MenuEvent

'''''''''''''''''''''''''''''''''''''''''''''''''
' add the second control to the Tools menu.
'''''''''''''''''''''''''''''''''''''''''''''''''
Set MenuEvent = New CVBECommandHandler
With Application.VBE.CommandBars("Menu Bar").Controls("Tools")
    Set CmdBarItem = .Controls.Add
End With
With CmdBarItem
    .Caption = "Second Item"
    .BeginGroup = False
    .OnAction = "'" & ThisWorkbook.Name & "'!Procedure_Two"
    .Tag = C_TAG
End With

Set MenuEvent.EvtHandler = Application.VBE.Events.CommandBarEvents(CmdBarItem)
EventHandlers.Add MenuEvent

End Sub

The code above creates two menu items on the Tools menu, and assigns the procedures Procedure_One and Procedure_Two to those menu items. A new CVBECommandHandler is created for each menu item and those instances are stored in the EventHandlers Collection.

When the menu items are created, the Tag property is set to the string in the constant C_TAG. You should change the value of this constant to something unique to your project. This value is used by the DeleteMenuItems procedure, shown below, to remove the menu items. 

Sub DeleteMenuItems()
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' This procedure deletes all controls that have a
' tag of C_TAG.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Ctrl As Office.CommandBarControl
    Set Ctrl = Application.VBE.CommandBars.FindControl(Tag:=C_TAG)
    Do Until Ctrl Is Nothing
        Ctrl.Delete
        Set Ctrl = Application.VBE.CommandBars.FindControl(Tag:=C_TAG)
    Loop
End Sub

SectionBreak

Creating The Procedures To Run

The final step is to write the procedures that are called by the menu items. When we created the menu items, we assigned the procedure Procedure_One to the OnAction property of the first menu item and Procedure_Two to the OnAction property of the second menu item. The CVBECommandHandler class will call the procedure specified in the OnAction property of the menu item. You can put these procedures in the same code module as the AddNewVBEControls procedure or you can put them in a separate module for organizational clarity. 

Public Sub Procedure_One()
    MsgBox "Procedure One"
End Sub

Public Sub Procedure_Two()
    MsgBox "Procedure Two"
End Sub

SectionBreak

Automatically Creating And Deleting The Menu Items

If you put the module above in a workbook that is automatically opened, such as your Personal.xls workbook, or in an add-in, you should call the function AddNewVBEControls from the Auto_Open procedure or the Workbook_Open event procedure. When the project is closed, you should call the DeleteMenuItems procedure from either the Auto_Close procedure or the Workbook_BeforeClose event procedure, as shown below. 

Public Sub Auto_Open()
    AddNewVBEControls
End Sub

Public Sub Auto_Close()
    DeleteMenuItems
End Sub

Download an example workbook with all the code.

This page last updated: 3-September-2007

-->