How to: Create Office Menus Programmatically

  • Article

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.

Note

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.

Note

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.

Example

' Declare the menu variable at the class level. 
Private WithEvents menuCommand As Office.CommandBarButton
Private menuTag As String = "A unique tag" 


' Call AddMenu from the Startup event of ThisWorkbook. 
Private Sub ThisWorkbook_Startup(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles Me.Startup

    CheckIfMenuBarExists()
    AddMenuBar()
End Sub 


' If the menu already exists, remove it. 
Private Sub CheckIfMenuBarExists()
    Try 
        Dim foundMenu As Office.CommandBarPopup = _
            Me.Application.CommandBars.ActiveMenuBar.FindControl( _
                Office.MsoControlType.msoControlPopup, System.Type.Missing, menuTag, True, True)

        If foundMenu IsNot Nothing Then
            foundMenu.Delete(True)
        End If 

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try 
End Sub 


' Create the menu, if it does not exist. 
Private Sub AddMenuBar()

    Try 
        Dim menuBar As Office.CommandBar = Application.comm.CommandBars.ActiveMenuBar
        Dim menuCaption As String = "Ne&w Menu" 

        If menuBar IsNot Nothing Then 
            Dim cmdBarControl As Office.CommandBarPopup = Nothing 
            Dim controlCount As Integer = menuBar.Controls.Count

            ' Add the new menu.
            cmdBarControl = CType(menuBar.Controls.Add( _
                Type:=Office.MsoControlType.msoControlPopup, Before:=controlCount, Temporary:=True),  _
                Office.CommandBarPopup)

            cmdBarControl.Caption = menuCaption
            cmdBarControl.Tag = menuTag

            ' Add the menu command.
            menuCommand = CType(cmdBarControl.Controls.Add( _
                Type:=Office.MsoControlType.msoControlButton, Temporary:=True),  _
                Office.CommandBarButton)

            With menuCommand
                .Caption = "&New Menu Command"
                .Tag = "NewMenuCommand"
                .FaceId = 65
            End With 
        End If 

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try 
End Sub 


' Add text to cell A1 when the menu is clicked. 
Private Sub menuCommand_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, _
    ByRef CancelDefault As Boolean) Handles menuCommand.Click

    Globals.Sheet1.Range("A1").Value2 = "The menu command was clicked." 
End Sub

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

See Also

Tasks

How to: Create Office Toolbars Programmatically

Walkthrough: Creating Shortcut Menus for Bookmarks

Concepts

Office UI Customization

The Variable missing and Optional Parameters in Office Solutions