| To work with command bars effectively, you'll likely need to change the way you think about menu bars, shortcut menus , and toolbars . In other words, instead of thinking of these as distinct objects, you need to start thinking of them as variations on the same theme. What theme? Well, for lack of anything better, how about the palette-of-controls-that-you-click-on-to-perform-an-action theme? Think about it. Whether it's a menu bar, a shortcut menu, or a toolbar, you interact with the object in the same way: You click a control (a menu bar item, a menu command, a toolbar button, and so on) and something happens (a menu pulls down, a command is executed, a submenu appears, a pop-up box appears, and so on). This, in a nutshell , is why Microsoft decided to gather menu bars, shortcut menus, and toolbars under the umbrella of the CommandBars object. Each of these items is now a CommandBar object and the only difference between them is that they have a different Type property. In a similar vein, the objects that can appear on a command bar ”menu commands, toolbar buttons , pop-up boxes, drop-down menus, and so on ”are called controls , and they're all variations of the CommandBarControl object. Specifying a Command Bar You use the CommandBars object to specify individual members ”that is, CommandBar objects ”of the collection. You can specify either an index number or the name of the command bar. For example, both of the following statements refer to the menu bar that appears when the focus is on an Excel worksheet: CommandBars(1) CommandBars("Worksheet Menu Bar") Unlike most collections, the index numbers used in the CommandBars object aren't all that useful. For example, Excel's Worksheets collection contains all the worksheets in a workbook, and the index numbers correspond to the order the sheets appear in the workbook. In the CommandBars collection, however, the index numbers for the built-in menus and toolbars have been assigned by the application's design team, so they have no intrinsic meaning. This means that you'll usually have to refer to individual CommandBar objects by their names : -
Custom CommandBar objects ” Any menus or toolbars that you create at runtime can also be named within the code. Therefore, your procedures will always know the names of these objects. -
Built-in toolbars ” The names of the application's built-in toolbars are easy enough to figure out: The name of the toolbar is just the text that appears in the toolbar's title bar (when the toolbar is floating, that is). -
Built-in menu bars and shortcut menus ” The application supplies a name for each built-in menu bar and shortcut menu, but there's no easy method for determining the names of these objects. Not knowing the names of the built-in menu bars and shortcut menus is a problem, to be sure. To help you solve that problem, Listing 14.1 presents an Excel procedure that runs through the entire CommandBars collection and displays the name, type, and index number for each command bar. Listing 14.1. A Procedure That Runs Through Excel's CommandBars Collection and Writes the Name, Type, and Index Number of Each Command Bar Sub ListExcelCommandBars() Dim i As Integer Dim cb As CommandBar Dim cbType As String i = 0 For Each cb In CommandBars Select Case cb.Type Case msoBarTypeNormal '0 cbType = "Toolbar" Case msoBarTypeMenuBar '1 cbType = "Menu Bar" Case msoBarTypePopup '2 cbType = "Shortcut Menu" End Select With Worksheets("Sheet1").[a2] .Offset(i, 0) = cb.Name .Offset(i, 1) = cbType .Offset(i, 2) = cb.Index End With i = i + 1 Next Set cb = Nothing End Sub  | Listing 14.1 and most of the other code in this chapter is available on my Web site: http://www.mcfedries.com/ABGVBA/Chapter14.xls For the modification to Listing 14.1 that works in Word, see the following file: http://www.mcfedries.com/ABGVBA/Chapter14.doc |
If you'd like to list the command bars in Word, substitute the With...End With statement in Listing 14.1 with the following: With ActiveDocument.Paragraphs(2).Range With .ParagraphFormat.TabStops .Add Position:=InchesToPoints(2) .Add Position:=InchesToPoints(3.5) End With .InsertAfter cb.Name & vbTab .InsertAfter cbType & vbTab .InsertAfter cb.Index .InsertParagraphAfter End With Creating a New Command Bar Whether you want to create a new toolbar, shortcut menu, or menu bar, the procedure is exactly the same. In other words, you invoke the Add method of the CommandBars object and use it to specify the type of command bar you want. Here's the syntax: CommandBars.Add( Name, Position, MenuBar, Temporary ) | Name | (optional) The name you want to use for the new command bar. Although this argument is optional, it's always a good idea to include it so that you can be sure of the command bar's name. Otherwise , the application assigns a generic name such as "Custom1." | | Position | (optional) Determines where the command bar appears within the application's window: | | | Position | Description | | | msoBarTop | Command bar is docked at the top of the window. | | | msoBarBottom | Command bar is docked at the bottom of the window. | | | msoBarLeft | Command bar is docked on the left side of the window. | | | msoBarRight | Command bar is docked on the right side of the window. | | | msoBarFloating | Command bar is undocked (this is the default). | | | msoBarPopup | Command bar is a shortcut menu. | | | msoBarMenuBar | (Macintosh only) Command bar replaces the system menu bar. | | MenuBar | (optional) A Boolean value that determines whether or not the new command bar replaces the active menu bar. Use True to replace the menu bar; use False to leave the active menu bar in place (this is the default). | | Temporary | (optional) A Boolean value that determines when the command bar is deleted. Use True to have the command bar deleted when the application is closed; use False to keep the command bar (this is the default). | For example, Listing 14.2 shows a procedure that uses the Add method to create a new temporary toolbar named My Toolbar. Before doing so, the procedure runs through the CommandBars collection to make sure there is no existing command bar with the same name. (The application generates an error if you attempt to create a new command bar with the name of an existing command bar.) Listing 14.2. A Procedure That Creates a New Toolbar After First Checking to See if a Command Bar with the Same Name Already Exists Sub AddToolbar() Dim cb As CommandBar Dim cbExists As Boolean cbExists = False For Each cb In CommandBars If cb.Name = "My Toolbar" Then cbExists = True Exit For End If Next cb If cbExists Then MsgBox "A command bar named ""My Toolbar"" already exists!" Else Set cb = CommandBars.Add( _ Name:="My Toolbar", _ Position:=msoBarFloating, _ Temporary:=True) End If Set cb = Nothing End Sub Command Bar Properties Whether you're dealing with one of the application's built-in command bars or a custom command bar that you've created via code, you can exploit a number of CommandBar object properties in your VBA procedures. Here's a look a few useful ones: CommandBar .BuiltIn ” Returns True if the specified CommandBar is native to the application; returns False for custom command bars. CommandBar .Controls ” Returns a CommandBarControls object that represents the collection of all the controls contained in the specified CommandBar . CommandBar .Enabled ” When this property is True, the user can work with the specified CommandBar . The command bar is disabled when this property is set to False. CommandBar .Height ” Returns or sets the height, in pixels, for the specified CommandBar . This property only has an effect on a non-empty command bar. Also, note that setting this property results in an error in two situations: -
If the command bar is docked (in other words, the command bar's Position property isn't set to msoBarFloating ; see the Position property, discussed later). -
If the command bar is protected against resizing (in other words, the command bar's Protection property is set to msoNoResize ; see the Protection property, discussed later). Here's a procedure fragment that checks a command bar's Position and Protection properties before changing the height: With CommandBars("My Toolbar") If .Position = msoBarFloating And _ Not .Protection = msoBarNoResize Then .Height = 100 End If End With CommandBar .Index ” Returns the index number in the CommandBars collection for the specified CommandBar . CommandBar .Left ” If the command bar is floating, this property returns or sets the distance, in pixels, of the left edge of the specified CommandBar from the left edge of the screen (not the application window). If the command bar is docked, this property returns the distance from the left edge of the docking area. CommandBar .Name ” Returns or sets the name of the specified CommandBar . CommandBar .Position ” Returns or sets the position of the specified CommandBar . This property uses the same constants that I outlined earlier for the Position argument in the CommandBars object's Add method. CommandBar .Protection ” Returns or sets the protection options for the specified CommandBar . You use these options to prevent (or allow) user customization of the object. When setting this property, use any one of the following constants (or you can apply multiple levels of protection by using the sum of two or more constants): | Protection | Value | Resulting Protection | | msoBarNoProtection | | None. | | msoBarNoCustomize | 1 | Prevents the user from adding, modifying, or deleting controls. | | msoBarNoResize | 2 | Prevents the user from resizing the command bar. | | msoBarNoMove | 4 | Prevents the user from moving the command bar. | | msoBarNoChangeVisible | 8 | Prevents the user from hiding or unhiding the command bar. | | msoBarNoChangeDock | 16 | Prevents the user from docking or undocking the command bar. | | msoBarNoVerticalDock | 32 | Prevents the user from docking the command bar on the left or right side of the window. | | msoBarNoHorizontalDock | 64 | Prevents the user from docking the command bar on the top or bottom of the window. | CommandBar .Top ” If the command bar is floating, this property returns or sets the distance in pixels of the top edge of the specified CommandBar from the top edge of the screen ( not the application window). If the command bar is docked, this property returns the distance from the top edge of the docking area. CommandBar .Type ” Returns the object type for the specified CommandBar , as follows : | Type | Type of Command Bar | | msoBarTypeNormal | Toolbar | | msoBarTypeMenuBar | Menu bar | | msoBarTypePopup | Shortcut menu | CommandBar .Visible ” Returns or sets whether or not the specified CommandBar is visible. Use True to display the command bar; use False to hide the command bar. Note that VBA sets this property to False by default when you create a custom command bar. CommandBar .Width ” Returns or sets the width, in pixels, for the specified CommandBar . This property only has an effect on a non-empty command bar and, as with Height , this property results in an error if the command bar is docked or if the command bar is protected against resizing. Deleting a Custom Command Bar Unless you specify otherwise, the command bars you create become permanent parts of the application. This might be a desirable situation in certain circumstances. For example, if your VBA project contains utilities that are useful for any document in the application, it makes sense to give you or a user full-time access to the procedures. On the other hand, your procedures might be applicable only while the VBA project is running. In this case, there are a number of reasons why you should delete the command bars when your project shuts down: -
To avoid confusing the user with extra command bars. -
To prevent damage to the user's other files that might be caused by running one of your procedures on a document that wasn't designed for your project. -
To save memory and resources. I mentioned earlier that you can create your command bars with the Add method's Temporary argument set to True. This tells the application to delete the command bar upon exiting. For immediate deleting, however, use the CommandBar object's Delete method: CommandBar .Delete Here, CommandBar is the custom CommandBar object that you want to delete. Note that you can't delete built-in command bars. Resetting a Built-In Command Bar If you make changes to one of the application's built-in command bars, you can restore the command bar to its default state by using the Reset method: CommandBar .Reset CommandBar is the built-in CommandBar object you want to reset. For example, Listing 14.3 shows the CleanUpCommandBars procedure that loops through the CommandBars collection and performs one of two tasks : If the command bar is built-in, it's restored to its default state; if the command bar is a custom object, it's deleted. Listing 14.3. A Procedure That Runs Through the CommandBars Collection, Resets the Built-in Command Bars, and Deletes the Custom Command Bars Sub CleanUpCommandBars() Dim cb As CommandBar For Each cb In CommandBars If cb.BuiltIn Then cb.Reset Else cb.Delete End If Next cb Set cb = Nothing End Sub | 