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

Posted on June 10, 2009


Introduction

This short how-to will show you how to create a toolbar 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 toolbar 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.CommandBar toolbar;
   private Office.CommandBarButton toolbarCommand;
   ...
}

Add Toolbar Method

Add the folloing method to your ThisAddIn.cs:

private void AddToolbar()
{
   try
   {
      toolbar = Application.CommandBars.Add(
         "My Toolbar",
         Office.MsoBarPosition.msoBarTop,
         false,
         true);
      if (toolbar != null)
      {
         toolbarCommand = (Office.CommandBarButton)
            toolbar.Controls.Add(
               Office.MsoControlType.msoControlButton,
               missing,
               missing,
               missing,
               true);
         toolbarCommand.Caption = "Toolbar Button";
         toolbarCommand.FaceId = 59;
         toolbarCommand.Click +=
            new Office._CommandBarButtonEvents_ClickEventHandler(
               toolbarCommand_Click);
         toolbar.Visible = true;
      }
   }
   catch (Exception e)
   {
      MessageBox.Show(e.Message);
   }
}

This method creates a new toolbar named “My Toolbar” and assigns it to the toolbar member field. Notice that true is passed into the last variable of the CommandBars.Add() method – this specifies that the toolbar is a temporary object. By specifying that the toolbar is temporary, it will automatically be removed from Excel when the add-in is closed, either implicitly by closing Ms 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 toolbar.

A toolbar button is then created using the previously created toolbar’s Controls.Add() method. The button is assigned to the toolbarCommand member field. A caption is set – this will be displayed when the user hovers their mouse over the toolbar button. The FaceId specifies the toolbar button icon – 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 toolbar button’s click event, which calls method toolbarCommand_Click (below).

This code has created the toolbar, but it will not be visible to the user unless explicitly instructed. Without setting the toolbar to visible here, the user would have to right click on the toolbar area and select the toolbar from a list of those available.

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 toolbarCommand_Click(Office.CommandBarButton Ctrl, ref bool CancelDefault)
{
   MessageBox.Show("Toolbar Command Click!");
}

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

Note: Your code should now compile.

Installing the Toolbar

If you run the add-in now, you will notice that the toolbar is not installed by the add-in. We must tell the add-in to add the toolbar. 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

   AddToolbar();
}

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

Run the Add-In

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

Toolbar Screenshot

Toolbar Screenshot

Clicking on the toolbar button will display the message box, as shown:

Message Box Screenshot

Message Box Screenshot

Posted in: Development