Basic Elements of Menu Items and Toolbar Buttons

Menu items, toolbar buttons, combo boxes, and edit controls on a command bar consist of two main elements:

The group of properties (such as a caption, a tooltip, an image, or an accelerator key) specific to the menu item, button, combo box, or other control types.

The item's response to an event, such as the user clicking the menu item or button, or changing the selected item in a drop-down list.

As might be inferred by the first element above, when you add a command bar control to a command bar, you set control properties such as caption, style, and tag. The second element of a command bar control, the response to an event that has occurred on the control, is determined by the control itself. For example, if the control is a menu item or a toolbar button, it responds to the click event. If the control is a combo box or edit control, it responds to the change event that occurs when the user selects an item in the drop-down list or enters a value in the box.

Basic Menu Item and Toolbar Button

Whether you add a simple menu item or a toolbar button, the code is the same. A menu item is just a button, but the button's caption is always displayed. In some cases, the menu item can contain an image next to the caption. When a menu item is copied onto a toolbar, the button style, by default, is set so that the user sees only the image, and not both image and caption.

Add a Simple Menu Item

The following procedure contains the most basic code you use to create a menu item. The code in the procedure can actually be written in one line, but the With…End block is used because this code could easily be extended to add more properties to the newly inserted menu item or toolbar button. This procedure works in all Office applications, because they all display a Tools menu. (In Outlook, you need to add .ActiveExplorer after Application in the first line.) To change the code so that it applies to a menu other than Tools menu, change the string "Tools" to the name of that other menu.

Sub InsertMenuItem() With Application.CommandBars("Tools").Controls.Add .Caption = "&Basic MenuItem" End With End Sub

Because the item's position in the menu isn't indicated in the Controls.Add method, the procedure will add the menu item to the end of the menu. If you want to place the menu item in a specific position, you can specify the optional Before parameter in the Add method of the CommandBarControls collection object. The first line from the procedure above would appear as follows if you specify the Before parameter and set it to the integer value of 1. The menu item control will be added as the first item in the menu.

With Application.CommandBars("Tools") _ .Controls.Add(Before:=1)

How Does the Code for a Toolbar Button Appear?

As described previously, the code for a toolbar button is exactly the same as the code for a menu item. By changing the string specified as the argument in the CommandBars property from "Tools" to "Standard," the command bar control that you add will be displayed as a toolbar button at the end of the Standard toolbar in Word, Excel, or PowerPoint. In the Visual Basic Editor in Word, Excel, or PowerPoint, click Module on the Insert menu, add the following code, and run the procedure.

Sub InsertButton() With Application.CommandBars("Standard") _ .Controls.Add(msoControlButton) .Caption = "&Basic Button" End With End Sub

To execute this procedure in a standard code module in the Visual Basic Editor in Access, change the string "Standard" to "Database." Access doesn't have a toolbar named "Standard." You also need to add a reference to the Microsoft Office 9.0 Object Library. Click References on the Tools menu and select Microsoft Office 9.0 Object Library from the Available References list box.

A Menu Item's Caption Property and the Accelerator Key

The Caption property is the most commonly set property of a command bar button or command bar control. When the Caption property is set, it'll also be used, by default, as the tooltip text—if the TooltipText property of a command bar control isn't set. When you're in customize mode for command bars, the Caption property is the same as the text entered in the Name edit control. When you're in customize mode and you right-click a control, the shortcut menu will display the properties for the control. You also use the caption when directly accessing a control in the controls collection of a command bar.

TIP
If clicking a menu item will display a dialog box, you should add an ellipsis (…) to the end of the menu item's caption string. (Microsoft Windows user interface guidelines indicate that you should use an ellipsis whenever clicking a menu item displays a dialog box.) The ellipsis tells a user that the menu item displays a dialog box instead of performing an action directly. The Save As command on the File menu in any of the Office applications, for instance, contains an ellipsis in the caption string and displays the Save As dialog box. The Copy command on the Edit menu, on the other hand, doesn't contain an ellipsis and copies the contents of the current selection to the Windows clipboard. For more information about user interface guidelines, see The Windows Interface Guidelines for Software Design (Microsoft Press, 1995).

You also use the Caption property to set the accelerator key for the control. The accelerator key is the character in the caption that's underlined when it's displayed on a menu or toolbar. When the user presses the ALT key plus the underlined character (the accelerator key), the focus is moved to that control. It's important to set the accelerator key so it can be employed by users who do not or cannot use a mouse. To set the accelerator key, in the caption string you need to include an ampersand (&) immediately preceding the character you want to set as the accelerator key for the control. In the procedure on the previous page (the one that inserted a menu item on the Tools menu), the first capital "B" is designated as the accelerator key.

NOTE
Assigning a distinct accelerator in the caption for a menu command in a built-in menu, such as the Tools menu in each Office application, often is not possible. For example, if you create a custom menu command on the Tools menu in Word with the caption "New Menu Item" and you assign the accelerator to the letter "N," you will have assigned the same accelerator as that in the caption of the Online Collaboration command. If there are more than one of the same accelerator values in a menu, pressing the accelerator multiple times will cycle through the menu items. You then have to press ENTER once you've found the menu item you're interested in.

Although command bars exhibit the ability to create custom menus and toolbars and add common controls such as buttons and combo boxes, there are a number of controls you can't add and some functionality you can't replicate. One common functionality you can't replicate is a shortcut key. Shortcut keys are used throughout Office and Windows, as shown in the following figure. Common built-in shortcut keys are CTRL+C to copy a selection (equivalent to clicking the Copy command on the Edit menu), CTRL+V to paste contents from the Windows clipboard, and CTRL+S to save the active document.

Quick Guide: Setting Up the Button Click Event
Once you've created the user interface elements, you need to provide a hook that connects a command bar control such as a toolbar button, menu item, or drop-down list to code that is executed when the user clicks or selects an item in the control. The following four steps explain what you need to do to connect a custom button on the Standard toolbar with an event procedure containing code that is executed when the button is clicked.
Add the following procedure to a standard code module and add the declaration to the top of the same code module:
Dim CmdBarEvents As New Class1

Sub InsertButtonAndConnectEvent()
    Dim ctlBtn As Office.CommandBarButton
    Set ctlBtn = CommandBars("Standard").Controls.Add
    With ctlBtn
        .Caption = "&Custom Button"
        .FaceId = 2950
        .Tag = "BrButton"
         Set CmdBarEvents.BtnCtrl = ctlBtn
    End With
End Sub
If you try to run this procedure, you'll get an error on the Set statement, or the last line in the procedure. If you just want to add a button on a toolbar, you should comment out the Set statement line of code by adding an apostrophe (') at the beginning of the line. The last line in the procedure, the Set statement, will be used to connect the click event of the button to a Click event procedure. You set up the Click event procedure after the first step.
Insert a new class module. By default, the name will be Class1.
Public WithEvents BtnCtrl As Office.CommandBarButton

Private Sub BtnCtrl_Click(ByVal Ctrl As _
    Office.CommandBarButton, CancelDefault As Boolean)
    MsgBox Ctrl.Caption & " was clicked"
End Sub
Remove the apostrophe (if one was added in step 1) and run the procedure InsertButtonAndConnectEvent.

Switch to the Office application and click the custom button.
At the top of the Class1 class module, you declare with events the variable BtnCtrl as a CommandBarButton. Using the WithEvents keyword of the Visual Basic for Applications language in a class module indicates that the BtnCtrl object variable will be used to respond to events triggered by a button. A CommandBarButton control in Office has only one event—a Click event, which is used when the user clicks a toolbar button or menu command.

At the top of the standard code module where you added the procedure for inserting the control (in step 1), you added the declaration line Dim CmdBarEvents As New Class1. The variable CmdBarEvents declares a new instance of the class Class1, which contains the event procedure for the command bar control you've declared with events. The declaration also ties in the line Set CmdBarEvents.BtnCtrl = ctlBtn, added at the end of the procedure in step 1. When you click the custom button, the code in the BtnCtrl_Click procedure (added in step 2) executes.