This documentation is archived and is not being maintained.

How to: Create Office Menus Programmatically

Applies to

The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office.

Project type

  • Document-level projects

  • Application-level projects

Microsoft Office version

  • Microsoft Office 2003

For more information, see Features Available by Application and Project Type.

This example creates a menu called New Menu on the menu bar in Microsoft Office Excel 2003. The new menu is placed before the Help menu. It contains one menu command. When the menu command is clicked, text is inserted into a cell on Sheet1.

For an example of how to customize the user interface in Microsoft Office Word 2003, see How to: Create Office Toolbars Programmatically and How to: Add Commands to Office Shortcut Menus.

Add the following code to the ThisWorkbook class.

NoteNote:

You must set the Tag property of your controls when you add event handlers. Office uses the Tag property to keep track of event handlers for a specific CommandBarControl. If the Tag property is blank, the events are not handled properly.

NoteNote:

Declare your menu variables at the class level instead of inside the method where they are called. This ensures that the menu variables will remain in scope as long as the application is running. Otherwise, the item is removed by garbage collection, and your event handler code stops working.

// Declare the menu variable at the class level. 
private Office.CommandBarButton menuCommand;
private string menuTag = "A unique tag";


// Call AddMenu from the Startup event of ThisWorkbook. 
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
    CheckIfMenuBarExists();
    AddMenuBar();
}


// If the menu already exists, remove it. 
private void CheckIfMenuBarExists()
{
    try 
    {
        Office.CommandBarPopup foundMenu = (Office.CommandBarPopup)
            this.Application.CommandBars.ActiveMenuBar.FindControl(
            Office.MsoControlType.msoControlPopup, System.Type.Missing, menuTag, true, true);

        if (foundMenu != null)
        {
            foundMenu.Delete(true);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}


// Create the menu, if it does not exist. 
private void AddMenuBar()
{
    try
    {
        Office.CommandBarPopup cmdBarControl = null;
        Office.CommandBar menubar = (Office.CommandBar)Application.CommandBars.ActiveMenuBar;
        int controlCount = menubar.Controls.Count;
        string menuCaption = "&New Menu";

        // Add the menu.
        cmdBarControl = (Office.CommandBarPopup)menubar.Controls.Add(
            Office.MsoControlType.msoControlPopup, missing, missing, controlCount, true);

        if (cmdBarControl != null)
        {
            cmdBarControl.Caption = menuCaption;
            cmdBarControl.Tag = menuTag;


            // Add the menu command.
            menuCommand = (Office.CommandBarButton)cmdBarControl.Controls.Add(
                Office.MsoControlType.msoControlButton, missing, missing, missing, true);

            menuCommand.Caption = "&New Menu Command";
            menuCommand.Tag = "NewMenuCommand";
            menuCommand.FaceId = 65;

            menuCommand.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(
                menuCommand_Click);
        }
    }
    catch (Exception e)
    {
        MessageBox.Show(e.Message);
    }
}


// Add text to cell A1 when the menu is clicked. 
private void menuCommand_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
{
    Globals.Sheet1.Range["A1", missing].Value2 = "The menu command was clicked.";
}
Show: