Sometimes your solution requires a set of custom menu commands. Adding a set of custom commands to an existing menu may expand the menu so much that it contains too many items. Introducing a new menu may be a good alternative. The Microsoft Office 9.0 Object Library provides an object model that allows you to manipulate any of the menus or toolbars provided by an Office application and to add custom menus and toolbars.
Creating your own toolbars by using Microsoft Visual Basic allows you to add elements to the user interface that, in turn, allow your customers to access the functionality in your programs. For example, if you develop a wizard that guides a user through the process of creating a specific type of Word document, you'll want to add a toolbar button or menu item that, when clicked, displays the custom wizard.
NOTE
The naming convention used in this chapter and throughout the rest of this book for command bar variables and command bar control variables is the following:
X and Y represent names that indicate what each control variable represents.
The Quick Guide on the next page provides a quick reference to creating the most common elements of a command bar set. These include adding a new menu, a submenu, a shortcut menu, a menu item, and a toolbar. The Quick Guide also describes how to write code to remove a toolbar. The section "Creating a Toolbar, Step by Step" later in this chapter explains the details of adding a new toolbar.
The following procedure reveals two ways of iterating through a command bar collection. The first uses a For Each…Next loop, and the second uses a regular For…Next loop. In the Visual Basic Editor in any Office application, click Module on the Insert menu, add the following code, and run the procedure. The procedure provides two different For…Next loops to iterate through the CommandBars collection object and print the name of each command bar to the Immediate window. You can use this procedure to determine if a specific command bar exists in the collection.
Sub IterateCommandBarCollection()
' For Each...Next loop
Dim cmbItem As CommandBar
For Each cmbItem In Application.CommandBars
Debug.Print cmbItem.Name
Next cmbItem
' OR
'For...Next loop
Dim i As Integer
For i = 1 To Application.CommandBars.Count
Debug.Print Application.CommandBars(i).Name
Next i
End Sub
|
To create a custom menu on the menu bar alongside the built-in menus in Word, Excel, PowerPoint, and other programs, you need to add a pop-up command bar control on the menu bar. The following procedure adds a new menu in between the File and Edit menus. But you can add menus to any toolbar, not just the main menu bar. If you change the string "Menu Bar" in the CommandBars property to "Standard," the new menu appears on the Standard toolbar with a drop-down arrow at the side of the caption. The Draw menu on the Drawing toolbar in Word, Excel, or PowerPoint is an example of a menu located on a toolbar.
NOTE
When running the following code in Excel, change the string "Menu Bar" to "Worksheet Menu Bar" in the CommandBars property:
Set cmbNewMenu = Application _ .CommandBars("Worksheet Menu Bar")In Outlook, you access the CommandBars collection through an Explorer or Inspector object:
Set cmbNewMenu = Application _ .ActiveExplorer.CommandBars("Menu Bar")An Explorer object is the window where you see folders such as the Inbox, Sent Items, and Deleted Items. An Inspector object is a window where you see items such as e-mail messages, meeting requests, and contact information.
In the Visual Basic Editor in any Office application, click Module on the Insert menu, add the following code, and run the procedure. To run the procedure in Excel or Outlook, make sure you change the code as indicated in the previous note. The procedure produces a custom menu with the caption "NewMenu" and two menu items.
Sub InsertMenu()
Dim cmbNewMenu As CommandBar
Dim ctlPopup As CommandBarPopup
Set cmbNewMenu = Application.CommandBars("Menu Bar")
With cmbNewMenu.Controls
Set ctlPopup = .Add(Type:=msoControlPopup, _
Before:=2)
With ctlPopup
.Caption = "&NewMenu"
With .Controls.Add
.Caption = "&Item 1"
End With
With .Controls.Add
.Caption = "&Item 2"
End With
End With
End With
End Sub
|
Your new menu should appear as follows:
The Menu Bar, which is itself represented by a CommandBar object, contains a Controls collection object that represents all of the pop-up menus, including File, Edit, View, Insert, and so on. The Set ctlPopup statement near the bottom of the previous page adds a new pop-up control to the Menu Bar command.
The Before argument of the Add method of the Controls collection object indicates the index position within the Controls collection where the menu is to be placed. In this case, a value of "2" places the new menu to the right of the File menu. To change the position of the new menu, change the integer value set to the Before argument. The Before argument is optional, so if the Before argument isn't specified, the new menu is added to the end of the menu (after the Help menu). In this case, the Set ctlPopup statement on the previous page would appear as:
Set ctlPopup = .Add(Type:=msoControlPopup) |
Adding a submenu is the same as creating a menu on the main menu bar. Examples of a submenu are the Macro submenu on the Tools menu and the Toolbars submenu on the View menu in all Office applications. To create a submenu, just change the string "Menu Bar" in the CommandBars property to any menu that exists on the menu bar or some other command bar. For example, if you change "Menu Bar" to "Tools," you create a submenu named "New Menu" containing the submenu at the second position of the Tools menu.
Sub InsertSubmenu()
Dim cmbMenu As CommandBar
Dim ctlPopup As CommandBarPopup
Set cmbMenu = Application.CommandBars("Tools")
Set ctlPopup = cmbMenu.Controls _
.Add(Type:=msoControlPopup)
With ctlPopup
.Caption = "&Submenu"
With .Controls.Add
.Caption = "Su&bmenuItem 1"
End With
With .Controls.Add
.Caption = "S&ubmenuItem 2"
End With
End With
End Sub
|
Your new submenu should appear as follows:
You should note that you can't create a "tear-off" submenu. An example of a tear-off menu is the AutoText submenu on the Insert menu in Word. It contains a small title bar that you can click and drag to create a floating or docked toolbar.
The following procedure inserts a new shortcut menu named NewShortcut. Like any menu, you can add any number of menu items that are of the command bar control type msoControlButton. In the Visual Basic Editor in any Office application, click Module on the Insert menu, add the following code, and run the procedure.
Sub InsertShortcutMenu()
Dim cmbNewShortcut As CommandBar
Set cmbNewShortcut = Application.CommandBars.Add( _
Name:="NewShortcut", Position:=msoBarPopup)
With cmbNewShortcut.Controls
With .Add
.Caption = "&ShortcutItem 1"
End With
With .Add
.Caption = "&ShortcutItem 2"
End With
End With
End Sub
|
You use the ShowPopup method to display shortcut menus. In the same code module where you typed the InsertShortcutMenu procedure, add the following code and run the DisplayPopup procedure. The shortcut menu NewShortcut, containing two items, is displayed wherever the mouse pointer is located on screen. If you switch back to the Office application, press ALT+F8 to display the Macros dialog box, and click DisplayPopup, the shortcut menu appears on top of the Office application window.
Sub DisplayPopup()
Application.CommandBars("NewShortcut").ShowPopup
End Sub
|
You can display a shortcut menu in response to a right-click event in Word, Excel, or PowerPoint. Word, Excel, and PowerPoint all support assigning an event procedure to the user action of right-clicking in any document. Chapter 10 describes how to set up the right-click event in Word, Excel, and PowerPoint.
To quickly delete the shortcut menu created by the procedure InsertShortcutMenu, type the following line of code in the Immediate window and press ENTER. Because the following line of code executes a method, the question mark (?) is not required before the beginning of the line.
Application.CommandBars("NewShortcut").Delete
|
Although adding a menu item is discussed at the beginning of this chapter, the procedure below is given for the sake of completeness to explain how to insert a new menu item at the top of the Tools menu. You'll set the menu item's style to display both the icon and caption—although not all menu items have or require an icon. The FaceId property sets the icon, and the value of 2950 isn't used by any built-in controls in Office, but reserved for custom use. In most cases, a FaceId value won't be set and an icon image won't be added to the control. The section later in this chapter entitled "Add an Image to a Control" describes more details and issues about adding images to command bar controls.
Sub InsertMenuItem()
With Application.CommandBars("Tools") _
.Controls.Add(Type:=msoControlButton, _
Before:=1)
.FaceId = 2950
.Caption = "&MenuItem"
.Tag = "MyCustomTag"
.Style = msoButtonIconAndCaption
End With
End Sub
|
To create a custom toolbar like the Standard or Formatting toolbar in Word, Excel, PowerPoint, or Outlook, you need to add a command bar to the CommandBars collection. The following procedure adds a new custom command bar and positions (or docks) the command bar below the Standard and Formatting toolbars. You add only one control, as specified in the With…End With block. You can add any number of controls to the new command bar by adding more Set statements that add controls to the command bar, and adding a With…End With block that sets the properties of the new controls.
Sub InsertToolBar()
Dim cmbNewBar As CommandBar
Dim ctlBtn As CommandBarButton
Set cmbNewBar = CommandBars.Add(Name:="NewBar")
With cmbNewBar
Set ctlBtn = .Controls.Add
With ctlBtn
.Style = msoButtonIconAndCaption
.BeginGroup = True
.Caption = "&Button"
.TooltipText = "ToolTip"
.FaceId = 59
End With
.Protection = msoBarNoCustomize
.Position = msoBarTop
.Visible = True
End With
End Sub
|
Your new toolbar should appear as follows:
The easiest way to remove your toolbar with Visual Basic code is to add this line of code to a procedure: Application.CommandBars("NewBar").Delete, and then execute the procedure. However, if the toolbar doesn't exist, an error results when the line is executed. In order to ensure that the toolbar is deleted if it exists and that no error arises if it does not, you can write code in one of two ways. (You can also include both methods.)
The following procedure traps the error that's generated when a line of code attempts to delete a command bar that doesn't exist. The first line in the procedure indicates that if an error occurs, execution of the procedure should skip to the line starting with the word "Error_Handler." (You can change the text "Error_Handler" to anything you want, as long as the colon (:) appears at the end of the line. If you do change the text, make sure to do so in both places in the procedure.) If no error occurs when the Delete method is executed, the procedure is exited. If the Exit Sub line didn't exist, execution of the procedure's lines of code would continue into the Error_Handler, even if no error occurred.
Sub DeleteCommandBarByErrorTrapping()
On Error GoTo Error_Handler
Application.CommandBars("NewBar").Delete
Exit Sub
Error_Handler:
' command bar does not exist
' Debug.Print Err.Description
End Sub
|
To delete a command bar if it exists and ensure that no error arises if it doesn't exist, you can use a For Each…Next loop to iterate through the command bars in the CommandBars collection. In the procedure below, the If…Then statement within the For Each…Next loop evaluates the name of each existing command bar. If the name of any command bar in the collection matches the name of your new command bar, the procedure deletes the first command bar and exits the loop.
Because the name of a command bar is unique within the CommandBars collection, only one command bar with the name you want to delete will exist. Exiting the loop after the command bar is deleted improves the performance of your code, because the loop doesn't need to continue looping through the collection. The only exception is in Word, where two command bars with the same name can exist; thus, you should remove the Exit For line from the procedure.
Sub DeleteCommandBarByIteration()
Dim cmbItem As CommandBar
For Each cmbItem In Application.CommandBars
If cmbItem.Name = "NewBar" Then
cmbItem.Delete
Exit For
End If
Next cmbItem
End Sub
|
Each Office application provides a collection of toolbars, and each toolbar helps you perform a set of common functions. For example, the Formatting toolbar consists of a set of buttons and drop-down lists that help you format text in your document, worksheet, or slide. You'll see the list of toolbars available in any particular Office application in the Toolbars tab of the Customize dialog box.
To access the Customize dialog box, on the Tools menu in any Office application, click Customize; or, on the View menu in Word, Excel, PowerPoint, or Access, point to Toolbars and then click Customize on the submenu. You can also right-click any toolbar and then click Customize on the shortcut menu. The Toolbars list displays all available toolbars, and the check box beside each item indicates whether or not that toolbar is visible.
Dim cmbNewBar As CommandBar Dim ctlBtn As CommandBarButton |
You're declaring the object variable cmbNewBar as the object type CommandBar. (Every toolbar and menu bar available to you in an Office application is considered a CommandBar object. Depending on what items you place on the command bar, it may appear as a menu bar, a toolbar, or a hybrid.) The second declaration is for a button control you're going to add to the newly created command bar.
Set cmbNewBar = Application.CommandBars.Add |
Each Application object in any Office application except Outlook provides access to the CommandBars collection object. The CommandBars collection object allows you to add a command bar to, or access an individual command bar from, the collection. In this case, the Add method of the CommandBars collection object returns a CommandBar object, which you assign to the object variable cmbNewBar.
cmbNewBar.Name = "My CommandBar" |
Each newly created CommandBar object is given a default name. Before you assign a name to a new command bar, the name appears with the syntax "Custom number," where number represents the next available integer. (The name of a floating command bar is the name that appears on its title bar; a floating command bar isn't docked to any side of an application window.) The name of a command bar also appears in the Toolbars list in the Toolbars tab of the Customize dialog box.
With cmbNewBar
Set ctlBtn = .Controls.Add(msoControlButton)
End With
|
Each CommandBar object contains a Controls collection object that represents all of the buttons, drop-down lists, pop-up menus, and other controls available on the command bar. When you create a command bar, however, no controls yet exist in the Controls collection: the Set statement above adds a button control. Note that the Add method of the Controls collection object accepts up to five parameters, with the first one representing the type of control to add. You can specify the types of parameter to be msoControlButton, msoControlEdit, msoControlDropdown, msoControlComboBox, or msoControlPopup.
With ctlBtn
.Style = msoButtonIconAndCaption
.BeginGroup = True
.Caption = "&Button"
.FaceId = 59
.TooltipText = "ToolTip"
End With
|
You can set a number of properties for a button on a command bar, but you have already set five of the main ones. The first is the button style, which allows you to represent a button as a combination of an icon and a caption (to the right of the icon), or an icon or a caption alone. To change the style of either of the latter choices, assign to the Style property the value msoButtonIcon or msoButtonCaption, respectively.
Because you've specified both an icon and a caption in the above procedure, both the Caption property and the FaceId property are set. More than 1,000 face Ids are built into Office. Most of them are the buttons on the toolbars that you see in the application window (such as New, Save, or Print). You use the TooltipText property to set the text of the ToolTip that appears when the mouse is over the button on the command bar.
.Protection = msoBarNoCustomize .Position = msoBarTop .Visible = True |
The Protection property is set to the constant msoBarNoCustomize, indicating that the command bar is protected from user customization. The Protection property can be set to be one or more of the MsoBarProtection constants. When the constant msoBarNoCustomize is assigned to the command bar, the user can neither add nor remove a command bar control to or from the command bar.
You can explicitly set the position of a command bar by using the Position property. You can set the Position property to one of the five values in the enumeration MsoBarPosition. Setting the property to msoBarTop docks the command bar at the top of the document window, while msoBarLeft, msoBarRight, and msoBarBottom dock the command bar at the left, right, and bottom, respectively. The Position property is set to the enumeration value msoBarFloating by default.
When you add a new command bar, you can also set its position. Note that the Add method of the CommandBars collection contains the optional Position parameter. However, if you don't specify the Position parameter in the Add method or explicitly set the Position property, a newly added command bar floats. Also note you can't use the enumeration value msoBarPopup to set the Position property. You can use it only when you add a command bar first. (If you specify the Position parameter in the Add method of the CommandBars collection as msoBarPopup, the command bar is added as a context menu.)
When you add a command bar, it's not visible by default; you have to explicitly set the command bar to Visible. The last line in the procedure above does so. The complete procedure for adding a command bar is as follows:
Sub InsertToolBarStepByStep()
Dim cmbNewBar As CommandBar
Dim ctlBtn As CommandBarButton
Set cmbNewBar = Application.CommandBars.Add
cmbNewBar.Name = "My CommandBar"
With cmbNewBar
Set ctlBtn = .Controls.Add(msoControlButton)
With ctlBtn
.Style = msoButtonIconAndCaption
.BeginGroup = True
.Caption = "&Button"
.TooltipText = "ToolTip"
.FaceId = 59
End With
.Protection = msoBarNoCustomize
.Position = msoBarTop
.Visible = True
End With
End Sub
|
The final toolbar should look like the following graphic, containing just one button displaying an icon and a caption. When you move the cursor over the button, you see the ToolTip.
