Walkthrough: Creating Custom Menu Items in Excel

 

Brian A. Randell
MCW Technologies, LLC

September 2003

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Excel 2003
    Microsoft Visual Studio .NET 2003

Summary: The Office CommandBar objects provide a way to hook custom code to menu items and toolbar buttons. In this walkthrough, you will create a custom menu bar with a custom menu item, and add code that runs in reaction to an Office menu item click. (8 printed pages)

Contents

Introduction
Prerequisites
Getting Started
Creating the Menu Bar Item
Creating the Menu Item
Hooking the Click Event of the Menu Item
Testing the Application
Conclusion

Introduction

Creating menu and toolbar items is a core feature of Microsoft® Office. Although this exercise demonstrates using these items within Microsoft Office Excel 2003, the behavior in Microsoft Office Word 2003 is much the same. (One big difference is that the main menu bar's name is Menu Bar in Word, as opposed to Worksheet Menu Bar in Excel.) You'll create a custom menu item on the Excel main menu bar. Then, you'll add a menu item. Finally, you'll hook the Click event of the menu item to execute custom code.

**Tip   **The Office object model for menu bars and toolbars is defined in the Office.dll primary interop assembly (PIA). Microsoft Visual Studio® .NET automatically includes a reference to this assembly when you create a new Visual Studio Tools for the Microsoft Office System project.

Prerequisites

To complete this walkthrough, the following software and components must be installed on the development computer:

  • Microsoft Visual Studio .NET 2003 or Microsoft Visual Basic® .NET Standard 2003
  • Microsoft Visual Studio Tools for the Microsoft Office System
  • Microsoft Office Professional Edition 2003

**Tip   **This demonstration assumes that if you're a Visual Basic .NET programmer, you've set the Option Strict setting in your project to On (or have added the Option Strict statement to each module in your project), although it is not required. Setting the Option Strict setting to On requires a bit more code, as you'll see, but it also ensures that you don't perform any unsafe type conversions. You can get by without it, but in the long run, the discipline required by taking advantage of this option will far outweigh the difficulties it adds as you write code.

Getting Started

In order to get started, you'll begin by creating a new Visual Studio .NET project that works with Excel.

Create the Project

Use Visual Studio Tools for the Microsoft Office System to create a new Excel Workbook project in Visual Basic .NET or Microsoft Visual C#®.

To create an Excel Workbook project

  1. Start Visual Studio .NET, and on the File menu, point to New, and click Project.

  2. In the Project Types pane, expand Microsoft Office System Projects, and then select Visual Basic Projects or Visual C# Projects.

  3. In the Templates pane, select Excel Workbook.

  4. Name the project ExcelCommandBars, and store it in a convenient local path.

  5. Accept the defaults in the Microsoft Office Project Wizard, and click Finish to create the project.

    Visual Studio .NET opens the ThisWorkbook.vb or ThisWorkbook.cs file in the Code Editor for you.

Creating the Menu Bar Item

Creating a menu bar item on the Excel main menu bar requires that you add a CommandBarControl object using the Add method of the main menu bar.

To create a menu bar item in Excel

  1. Add the following declarations immediately beneath the existing declarations for ThisApplication and ThisWorkbook variables:

    ' Visual Basic
    Private MainMenuBar As Office.CommandBar
    Private MenuBarItem As Office.CommandBarControl
    Private WithEvents MenuItem As Office.CommandBarButton
    
    // C#
    private Office.CommandBar MainMenuBar = null;
    private Office.CommandBarControl MenuBarItem = null;
    private Office.CommandBarButton MenuItem = null;
    
  2. Within the OfficeCodeBehind class (created for you by the project template), add the following procedure, which initializes the MainMenuBar and MenuItemBar objects declared earlier:

    ' Visual Basic
    Private Sub InitMenuBarItems(ByVal Caption As String)
        Try
            MainMenuBar = ThisApplication.CommandBars( _
                "Worksheet Menu Bar")
            MenuBarItem = MainMenuBar.Controls.Add( _
                Office.MsoControlType.msoControlPopup, Temporary:=True)
            MenuBarItem.Caption = Caption
    
        Catch ex As Exception
            MessageBox.Show(ex.Message, _
                ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
    
        End Try
    End Sub
    
    // C#
    private void InitMenuBarItems(string Caption) 
    {
        try 
        {
            MainMenuBar = 
                ThisApplication.CommandBars["Worksheet Menu Bar"];
            MenuBarItem = MainMenuBar.Controls.Add(
                Office.MsoControlType.msoControlPopup, 
                Type.Missing, Type.Missing, Type.Missing, true);
            MenuBarItem.Caption = Caption;
    
        }
        catch (Exception ex) 
        {
            MessageBox.Show(ex.Message,
                ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
    
  3. Add the following code to the pre-existing ThisWorkbook_Open procedure. This code calls the InitMenuBarItems procedure that you've just created:

    ' Visual Basic
    InitMenuBarItems("&Custom Code")
    
    // C#
    InitMenuBarItems("&Custom Code");
    
  4. Select Save All on the File menu to save the entire solution.

  5. Press F5 to run the project, loading Excel and your workbook.

  6. Within Excel, check to see that a menu bar item labeled Custom Code appears to the right of the Help menu as shown in Figure 1.

    Figure 1. Excel with a custom menu bar item

Creating the Menu Item

With the custom menu bar item in place, you're ready to put a menu item on the new menu. Menu items are represented as CommandBarControl objects. You will create a new CommandBarControl instance using the Add method of the previously created menu bar item's Controls collection.

To add a menu item

  1. Add the following procedure to the OfficeCodeBehind class. This procedure creates the CommandBarControl and sets its caption:

    ' Visual Basic
    Private Function CreateButton( _
        ByVal Parent As Office.CommandBarPopup, _
        ByVal Caption As String) As Office.CommandBarButton
    
        Try
            Dim cbc As Office.CommandBarControl
            cbc = Parent.Controls.Add( _
                Office.MsoControlType.msoControlButton, Temporary:=True)
            cbc.Caption = Caption
            cbc.Visible = True
    
            Return DirectCast(cbc, Office.CommandBarButton)
    
        Catch ex As Exception
            MessageBox.Show(ex.Message, _
                ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
    
        End Try
    End Function
    
    // C#
    private Office.CommandBarButton CreateButton(
        Office.CommandBarPopup Parent, string Caption) 
    {
        Office.CommandBarControl cbc = null;
        try
        {
            cbc = Parent.Controls.Add(
                Office.MsoControlType.msoControlButton, Type.Missing, 
                Type.Missing, Type.Missing, true);
            cbc.Caption = Caption;
            cbc.Visible = true;
    
        }
        catch (Exception ex) 
        {
            MessageBox.Show(ex.Message,
                ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        return (Office.CommandBarButton) cbc;
    }
    
  2. Add the following line of code to the to the ThisWorkbook_Open procedure, following the line of code which calls the InitMenuBarItems procedure:

    ' Visual Basic
    MenuItem = CreateButton( _
        DirectCast(MenuBarItem, Office.CommandBarPopup), _
        "Run Demo Code")
    
    // C#
    MenuItem = CreateButton( 
        (Office.CommandBarPopup)MenuBarItem, 
        "Run Demo Code");
    
  3. Select Save All on the File menu to save the entire solution.

  4. Press F5 to run the project, loading Excel and your workbook.

  5. Within Excel, check to see that the Run Demo Code menu item appears when you click the custom top-level menu item, as shown in Figure 2.

    Figure 2. After adding a menu item

Hooking the Click Event of the Menu Item

To complete this walkthrough, you need to add an event handler that will execute when the custom menu item button is clicked.

(Visual Basic Only) Hook the Click Event of the Menu Item

Complete the following steps to add an event handler in Visual Basic .NET that will execute when the custom menu item button is clicked.

To add an event handler for the custom menu item (Visual Basic)

  1. From the Class Name drop-down list in the upper-left corner of the Code Editor, select MenuItem.

  2. From the Method Name drop-down list in the upper-right corner of the Code Editor, select Click.

    Visual Studio .NET creates the event handler stub for you.

  3. Modify the MenuItem_Click procedure, adding the following code:

    ' Visual Basic
    MessageBox.Show(String.Format( _
        "You just clicked the button labeled '{0}'.{1}" & _
        "The name of your workbook is '{2}'.", _
        Ctrl.Caption, Environment.NewLine, ThisWorkbook.Name), _
        "MenuItem_Click", MessageBoxButtons.OK, _
        MessageBoxIcon.Information)
    
  4. Select Save All on the File menu to save the entire solution.

  5. Press F5 to run the project, loading Excel and your workbook.

  6. Within Excel, click the Custom Code menu, and select Run Demo Code.

    An alert appears describing the current workbook.

(C# Only) Hook the Click Event of the Menu Item

Complete the following steps to add an event handler in Visual C# that will execute when the custom menu item button is clicked.

To add an event handler for the custom menu item (C#)

  1. Add the following procedure to the OfficeCodeBehind class:

    // C#
    private void MenuItem_Click(
        Office.CommandBarButton Ctrl, ref Boolean CancelDefault) 
    {
        MessageBox.Show(String.Format(
            "You just clicked the button labeled '{0}'.\n" + 
            "The name of your workbook is '{1}'.", Ctrl.Caption,
            ThisWorkbook.Name), "MenuItem_Click", MessageBoxButtons.OK, 
            MessageBoxIcon.Information);
    }
    
  2. Modify the ThisWorkbook_Open procedure, adding the following code:

    // C#
    MenuItem.Click += 
        new Microsoft.Office.Core.
        _CommandBarButtonEvents_ClickEventHandler(MenuItem_Click);
    

    **Tip   **If you type the first portion of the line of code (up to and including the +=), Visual Studio .NET will prompt you to press TAB, and the Code Editor will insert the remainder of the line for you. This new feature, providing automatic hookup of events, makes it easy to hook up event handlers to existing procedures.

Testing the Application

Now you can test the custom menu item that you created.

To test the application

  1. Select Save All on the File menu to save the entire solution.

  2. Press F5 to run the project, loading Excel and your workbook.

  3. Within Excel, click the Custom Code menu, and select Run Demo Code.

    An alert appears describing the current workbook.

Conclusion

A core feature of Microsoft Office applications is the ability to create menu and toolbar items. The Office CommandBar objects provide a way to hook custom code to menu items and toolbar buttons. Although this walkthrough demonstrates how you can add code that runs in reaction to an Excel menu item click, you will find that the behavior in Word is much the same.