Home > .NET, C#, VSTO > VSTO: How to Create a Menu for an Excel Add-In

VSTO: How to Create a Menu for an Excel Add-In

June 11, 2009 grwilliamson

Introduction

This short how-to will show you how to create a menu as part of a Microsoft Visual Studio Tools for Office (VSTO) Excel 2003 Add-In.

It is assumed that you have Microsoft Visual Studio and the Microsoft Visual Studio Tools for Office SDK installed. The project in this tutorial was created using Microsoft Visual Studio 2008, developing with C#.

Create a new Project

If you have an existing VSTO Excel add-in project you want to add a menu to, skip to the next section.

Otherwise:

  1. Open Microsoft Visual Studio.
  2. Select File > New > Project.
  3. From the New Project window select Visual C# > Office > 2003. (If you can’t see the Office branch of the Visual C# tree, you don’t have VSTO installed).
  4. Select Excel 2003 Add-in.
  5. Name the add-in project ExcelAddIn1.
  6. Click OK.
  7. Open the ThisAddIn.cs file.

Using Statements

It is assumed throughout this tutorial that the following using statements are present:

using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

These are normally automatically generated by the VSTO project.

Add-In Callbacks

It is assumed that you have something similar to the following in your Excel Add-In code file, named for example ThisAddIn.cs:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
   #region VSTO generated code
   this.Application = (Excel.Application)
      Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(
         typeof(Excel.Application), this.Application);
   #endregion
}
...
private void ThisAddIn_Shutdown(object sender, System.EventArgs e) { }
...
#region VSTO generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InternalStartup()
{
   this.Startup += new System.EventHandler(ThisAddIn_Startup);
   this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion

Member Fields

Create the following member fields in your ThisAddIn.cs:

public partial class ThisAddIn
{
   ...
   private Office.CommandBarPopup popupMenu;
   private Office.CommandBarButton menuCommand;
   ...
}

Add Menu Method

Add the following method to your ThisAddIn.cs:

private void AddMenuBar()
{
   try
   {
      Office.CommandBar activeMenuBar = (Office.CommandBar)
         Application.CommandBars.ActiveMenuBar;
      int controlCount = activeMenuBar.Controls.Count;

      popupMenu = (Office.CommandBarPopup)
         activeMenuBar.Controls.Add(
            Office.MsoControlType.msoControlPopup,
            missing,
            missing,
            controlCount,
            true);

      if (popupMenu != null)
      {
         popupMenu.Caption = "&My Menu";

         menuCommand = (Office.CommandBarButton)
            popupMenu.Controls.Add(
               Office.MsoControlType.msoControlButton,
               missing,
               missing,
               missing,
               true);

         menuCommand.Caption = "&Do Something";
         menuCommand.FaceId = 59;

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

The first line gets a reference to the active menu bar. The method then uses this to create and add a menu to the menu bar with the caption “My Menu”. The newly created menu is assigned to the popupMenu member field. Notice that true is passed into the last variable of the Controls.Add() method – this specifies that the menu is a temporary object. By specifying that the menu is temporary, it will automatically be removed from Excel when the add-in is closed, either implicitly by closing Excel or by forceably removing the add-in. The benefit of this is that you don’t need to write any code to explicitly remove the menu.

A menu item (CommandBarButton) is then created using the previously created menu’s Controls.Add() method. The button is assigned to the menuCommand member field. A caption is set – this it the text visible in the menu item. The FaceId specifies an icon to display as part of the menu item (to the left of the menu item caption) which can be one of these FaceIds. Notice again that true is specified for the temporary argument of the Controls.Add() method.

An event handler is wired up to the menu button’s click event, which calls method menuCommand_Click (below).

Note: Your code won’t compile until you follow the next step.

Event Handler Method

Add the following method to your ThisAddIn.cs:

private void menuCommand_Click(Office.CommandBarButton Ctrl, ref bool CancelDefault)
{
   MessageBox.Show("Menu Command Click!");
}

This is the event handler wired up to the menu item click. It simply displays a message to the user each time the menu item is clicked.

Note: Your code should now compile.

Installing the Menu

If you run the add-in now, you will notice that the menu does not appear in the main menu bar as desired. We must tell the add-in to add the menu. To do this, modify the startup callback of your ThisAddIn.cs to match the following:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
   #region VSTO generated code
   this.Application = (Excel.Application)
      Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(
         typeof(Excel.Application), this.Application);
   #endregion

   AddMenuBar();
}

That is, add the call to AddMenuBar() to the existing code.

Run the Add-In

Try running your project in debug mode. You should see a new menu with a single item like so:

Menu Screenshot

Menu Screenshot

Clicking on the menu item will display the message box, as shown:

Message Box Screenshot

Message Box Screenshot

Possibly related posts: (automatically generated)

Categories: .NET, C#, VSTO Tags: , , , , , , , ,