This documentation is archived and is not being maintained.

How to: Create Office Menus Programmatically

NoteNote

Some code examples in this topic use the this or Me keyword or the Globals class in a way that is specific to document-level customizations, or they rely on features of document-level customizations such as host controls. These examples can be compiled only if you have the required applications installed. For more information, see Features Available by Product Combination.

This example creates a menu called New Menu on the menu bar in Microsoft Office Excel 2003. The new menu is placed to the left of 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 customizing the user interface in Microsoft Office Word 2003, see How to: Create Office Toolbars Programmatically and Walkthrough: Creating Shortcut Menus for Bookmarks.

Add the following code to the ThisWorkbook class.

NoteNote

You must set the Tag property on 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 at some indeterminate point, and your event handler code stops working.

Example

// 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;

            // 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.";
}

See Also

Show: