This is the sixth of nine installments in the Build an Excel Add-In series. In previous installments, we wrote some code to perform a useful task, enhanced its functionality to make it more broadly applicable, modularized the code and included an auxiliary modular procedure, built a dialog to ask the user for preferences, and brought all the code together. What remains is to construct the user interface elements for Classic Excel (2003 and earlier) and for new Excel (2007 and later), tie up a few loose ends, and create a setup file for easy deployment. A linked outline can be found at the end of this article.

Microsoft Office User Interfaces

Microsoft introduced a flexible and powerful user interface system in Office 97, and this interface has remained in use through Office 2003 (and a subset remains in use in later versions). The user interface consists of various command bars, which include menu bars and toolbars. Command bars can contain menus as well as buttons. Menus can contain submenus and buttons. Submenus can contain, well, you get the idea. This system of menus, toolbars, and buttons is easily customized through the user interface and via VBA.

Here is what our add-in’s menu customizations look like. This article shows how to create these customizations.

The Microsoft Office development team at Microsoft noticed some things about the command-bar-style interface, and about how users interacted with it. The interface was becoming complicated, with three or more levels of menus and submenus existing in some places. Each application ended up with its Tools menu, a kitchen junk drawer of commands that didn’t logically fit into the other menus. Users didn’t know what all the features were, or couldn’t find them, if they were buried more than a level deep, so they kept suggesting new features which already existed. Many users also didn’t know how to clean up their interfaces, so there were too many rows of docked toolbars and too many floating toolbars obstructing the workspace.

In response to the complicated nature of the command-bar-based interface and to the inability of some users to learn to use it as intended, the Microsoft Office team gutted the interface, replacing most toolbar implementations in favor of a new Ribbon in Office 2007. The ribbon is controlled using XML instead of VBA. Instead of toolbars which are visible all or most of the time, the ribbon has a set of different tabs. These tabs contain fewer buttons, and the buttons used more tend to be larger. The tabs become visible when the Office program thinks you might want to use them, and invisible when you actually do want to use them. No parts of the new interface can be undocked, so users who didn’t know how to click the close box of a floating toolbar now have clean interfaces, while users who like dispatching toolbars and palettes to areas on the screen where they are needed have to resort to extra mouse travel distances and excess mouse clicks. The ribbon-based interface is easy to customize using the RibbonX XML dialect and related VBA callbacks, but it is practically inaccessable through the user interface. Unless, of course, you count the QAT (Quick Access Toolbar), which is like a Classic toolbar on strong sedatives. Fortunately the ability of a user to customize their own interface was resuscitated for use in Office 2010.

This article will show how to construct a simple set of Classic Excel menu and toolbar customizations using VBA. The next article in the sequence will show simple XML code and VBA callbacks for use in New Excel.

Classic Excel User Interface Code

The object model for the Office command bar system is straightforward. The CommandBars collection contains menu bars, standard toolbars, and popup menus (context or right-click menus). There is a lot of documentation of this system on the internet. This article shows a set of simple, hard-coded routines, all that is necessary for this simple add-in. There are a number of examples of more elaborate and flexible routines that use tables in Excel workbooks to define all of the interface elements to be created. John Walkenbach describes Creating Custom Menus using his MenuMakr system. I started with his system, but like any good engineer, I tweaked it beyond all recognition to create the system I use in my own add-ins and in projects for my clients.

Here is the simple code in its entirety, copied out of its code module. Explanations for selected sections of the code follow the complete listing.

''========================================================================
'' Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
''========================================================================
Option Explicit

Const sMenuName As String = "&PTS Charts"
Const sToolbars As String = "Worksheet Menu Bar|Chart Menu Bar|PTS Charts"
Const sButton As String = "PT_Plotter"
Const sOnAction As String = "PT_Plotter_Dialog"

Sub Create_PTPlotter_Menu()
  Dim ctlpop As CommandBarPopup
  Dim ctlbtn As CommandBarButton
  Dim iMenu As Long
  Dim vMenu As Variant

  Delete_PTPlotter_Menu

  vMenu = Split(sToolbars, "|")

  For iMenu = LBound(vMenu) To UBound(vMenu)
    On Error Resume Next
    If Application.CommandBars(vMenu(iMenu)) Is Nothing Then
      With Application.CommandBars.Add(vMenu(iMenu), msoBarFloating)
        .Left = Application.Left + Application.Width / 4
        .Top = Application.Top + 144
        .Visible = True
      End With
    End If
    On Error GoTo 0

    With Application.CommandBars(vMenu(iMenu))
      On Error Resume Next
      If .Type = msoBarTypeMenuBar Then
        Set ctlpop = .Controls(sMenuName)
        On Error GoTo 0
        If ctlpop Is Nothing Then
          Set ctlpop = .Controls.Add(10, 1, , , True)
          With ctlpop
            .Caption = sMenuName
            .Visible = True
          End With
        End If
        Set ctlbtn = ctlpop.Controls.Add(1, 1, , , True)
      Else
        Set ctlbtn = .Controls.Add(1, 1, , , True)
        .Visible = True
      End If

      With ctlbtn
        .Caption = sButton
        .OnAction = "'" & ThisWorkbook.Name & "'!" & sOnAction
        .TooltipText = "Create chart from arbitrary data range"
        .FaceId = 422
        .Visible = True
      End With
      Set ctlpop = Nothing
    End With
  Next
End Sub

Sub Delete_PTPlotter_Menu()
  Dim ctlpop As CommandBarPopup
  Dim ctlbtn As CommandBarControl
  Dim iMenu As Long
  Dim vMenu As Variant

  On Error Resume Next
  vMenu = Split(sToolbars, "|")

  For iMenu = LBound(vMenu) To UBound(vMenu)
    With Application.CommandBars(vMenu(iMenu))
      If .Type = msoBarTypeMenuBar Then
        Set ctlpop = .Controls(sMenuName)
        Do
          Set ctlbtn = ctlpop.Controls(sButton)
          If ctlbtn Is Nothing Then Exit Do
          ctlbtn.Delete
          Set ctlbtn = Nothing
        Loop

        If ctlpop.Controls.Count = 0 Then
          ctlpop.Delete
        End If
      Else
        Do
          Set ctlbtn = .Controls(sButton)
          If ctlbtn Is Nothing Then Exit Do
          ctlbtn.Delete
          Set ctlbtn = Nothing
        Loop

        If .Controls.Count = 0 Then
          .Delete
        End If

      End If
    End With
  Next

End Sub

Explanations

The module starts by defining a few constants. The menu name sMenuName, “&PTS Charts” has an ampersand that tells Excel to use the next character (P) as an accelerator key, that is, to activate the menu when Alt plus the next character is pressed (Alt+P). This character will appear underlined in the menu name: “PTS Charts”.

sToolBars is a pipe-delimited string of command bars which will be customized. The remaining constants list the button caption and the name of the procedure to be run when the button is clicked.

Create_PTPlotter_Menu is the routine that adds our buttons to the Classic Excel interface. The first command in this routine is Delete_PTPlotter_Menu, which removes any existing buttons from this add-in which have somehow been orphaned due to a disorderly shutdown.

The pipe-delimited string is converted into an array of command bar names. The program loops through this array, looking for command bars with each name. If such a command bar is not found, then a new standard command bar is created using that name, other wise the program continues with the found command bar.

If the command bar is a menu bar, the program looks for a menu with the indicated name. If such a menu is not found on the command bar, a new menu is created, otherwise the existing menu is used. Then the button is placed on the menu. If the command bar is a regular toolbar, the button is placed directly on the toolbar.

The caption of the button is assigned, as is the name of the procedure to run when the button is clicked. The procedure name is prepended with the workbook name in single quotes, so Excel will not confuse our procedure with a procedure with the same name in another workbook.

The button is assigned its tooltip text, which appears when the cursor passes over the button.

Built-in button image number 422 is used for the button’s icon. Display FaceIDs is a simple utility that helps you browse Excel’s button images. Not described here is the means to assign a custom button image.

Finally the button is made visible, so users can find it.

Delete_PTPlotter_Menu goes through the command bars listed in the pipe-delimited string, looking for buttons with the name used by our add-in. This is done inside Do loops, in case multiple crashes left behind multiple identical buttons. If the last button of a menu or toolbar is deleted, the empty menu or toolbar is also deleted.

Our Menu and Toolbar

The menu and toolbar customizations produced by the code above are displayed below.

The menu is on the Worksheet Menu Bar; the menu on the Chart menu Bar is the same. The PTS Charts menu and the PTS Charts toolbar already existed, with a variety of my utilities displayed, and the PT_Plotter button is added at the end of the list.

Contents: How to Build an Excel Add-In

• Build an Excel Add-In 1 – Basic Routine
• Build an Excel Add-In 2 – Enhanced Functionality
• Build an Excel Add-In 3 – Auxiliary Modular Functions
• Build an Excel Add-In 4 – Create the Dialog
• Build an Excel Add-In 5 – Tie the Code Together
• Build an Excel Add-In 6 – Interface for 2003
• Build an Excel Add-In 7 – Interface for 2007
• Build an Excel Add-In 8 – Last Steps
• Build an Excel Add-In 9 – Deployment

Create_LblLastPt_Menu