Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Table-Driven Command Bars

For small-scale Excel applications with a few toolbar buttons and/or menu items, it is perfectly acceptable to hard-code the creation of the command bars and controls your application requires using a custom VBA procedure. (We do recommend that you isolate command bar building code in a separate procedure for ease of maintenance.) When you begin building large-scale applications with multiple dedicated toolbars and menus, hard-coded command bars become very time-consuming to create and difficult to maintain.Chapter 4 Worksheet Design, table-driven is a generic term referring to any process that performs some operation guided by information stored in a dedicated table on a worksheet.

Table-driven command bar building is one of the more complex table-driven methodologies. Implemented correctly, however, it is easy to use and far superior to anything that can be accomplished using VBA alone. Even when we resort to using Visual Basic ActiveX DLLs, a technique we cover extensively in Chapter 20 Combining Excel and Visual Basic 6, we use an Excel add-in workbook with a three-line stub procedure assigned to each of the command bar controls that calls the ActiveX DLL. This add-in workbook allows us to use worksheets, which would otherwise be unavailable, to implement table-driven command bars and other table-driven methodologies.

In this section we describe the most sophisticated table-driven command bar builder available. As with all other applications described in the book, this command bar builder is included on the CD that accompanies this book and can be integrated into any of your applications.

Introducing the Table-Driven Command Bar Builder

The table-driven command bar builder consists of three parts that form a self-contained unit you can plug directly into any application: a worksheet table that defines the command bars and controls to be built and two code modules that read this table and build the command bars and controls it specifies.Putting It All Together section later in the chapter we demonstrate the workbook containing the version of the command bar builder that you should use in your projects.

After you have added definitions of the command bars and controls you want to build to the wksCommandBars worksheet, your application just needs to call one procedure on startup and all of those command bars and controls will be built to your specifications. A second procedure can be called on shutdown to dismantle the command bars and controls specified in the table. We cover this in more detail in the Practical Example section at the end of the chapter.

We spend most of this section explaining how to write command bar definitions in the command bar definition table. It's best to think of the code that reads and implements the command bars and controls defined by the table as a black box. This code is too lengthy and complex to cover in any detail in this chapter, but you are strongly encouraged to examine the code if you want to understand how it works. The code is open and heavily commented, so it should be reasonably approachable to the seasoned programmer.

The Command Bar Definition Table

The reason we are spending so much time describing the command bar definition table is because you will spend 99 percent of your command bar building time working with it. The only thing you need to do with the code, after adding it to your project, is call the build command bars procedure on startup and the reset command bars procedure on shutdown.

The command bar definition table is too wide to display entirely in a screen shot on the printed page, but we will give you the flavor for what it looks like with the partial example shown in Figure 8-1.

Figure 8-1. A Partial Command Bar Definition Table

[View full size image]

The custom menu bar created by this command bar definition table entry is shown in Figure 8-2.

Figure 8-2. A Custom Menu Bar Created by the Command Bar Builder

Keep in mind this is only a small section from the upper-left corner of the command bar definition table. The actual table contains at least 25 columns of settings and as many rows as required to define all of the command bars and controls required for the application.

One of the features making the command bar definition table so easy for experienced programmers to use is that it contains sheet-level defined constants corresponding to every enumeration member used when building command bars and controls with VBA. Therefore, wherever the table calls for an enumeration member such as msoBarTop or msoButtonIconAndCaption, you can use those names exactly as you would in VBA by just preceding them with an equal sign when you enter them into a cell. Their numeric values will appear in the cell, but if you select the cell and check the formula bar you will see the enumeration member name. Figure 8-3 shows an example of this.

Figure 8-3. Defined Constants in the Command Bar Definition Table

In the sections that follow, we will provide complete descriptions of the purpose and usage of each of the settings in the command bar definition table. In the actual table there are cell comments at the top of each column that give brief but reasonably complete explanations of the purpose of the column. This will enable you to use the table without having to continually refer back to this chapter. The setting names in the column headers of the command bar definition table are identical or very similar to the names of the VBA properties they represent on the command bar or control being defined. This allows you to leverage all of your existing knowledge of how to build command bars and controls in VBA when using the table-driven command bar builder.

Most settings in the command bar definition table are not required. In addition, some settings apply to CommandBar objects, some to CommandBarControl objects and some to both. Table 8-1 contains a summary of the command bar definition table settings, showing which objects each setting applies to, which settings are required and what the default value is for each optional setting, if any.

Beginning with the Position column and continuing through the last column in the table, the column values apply to the command bar or control whose name or caption is specified in the same row in one of the first four columns of the table. If a setting does not apply to the type of object specified in the initial columns, it will be ignored. Similarly, if the entry in the initial columns specifies an existing command bar or control object, most settings in subsequent columns are ignored.

Command Bar Name

The command bar builder has the flexibility to create new command bars as well as add controls to existing command bars. Regardless of whether you are creating a new command bar or adding controls to an existing command bar, you enter the name of the command bar in this column. The command bar builder checks each command bar name specified in this column to see whether it already exists. If the specified command bar already exists, the command bar builder assumes you are adding controls to that command bar. If no command bar with the specified name exists, the command bar builder creates a new command bar with the specified name using the settings specified in later columns.

There must be two entries in the Command Bar Name column for every command bar you build or modify. The first entry must be the name or index number of the command bar being created or modified. The second entry is simply the word Stop. The Stop entry must be placed in the row directly below the last row of the command bar definition specified by the first entry. These two entries in the first column work together to bracket the command bar definition so the command bar building code knows where the definition starts and ends.

In Figure 8-1, notice how the command bar name Custom Menu Bar is placed at the top of the Command Bar Name column in cell A2 and the word Stop is placed at the bottom in cell A27. As shown in this example, there cannot be any entries between the command bar name value and the Stop keyword. You can stack as many command bar definitions in the table as you like. The only rule is that each subsequent definition must be separated from the previous definition by at least one blank row.

Control Caption

There are three Control Caption columns by default. This is because good user interface design suggests you should not use more than three cascading menu levels. If you really must have additional levels, you can simply insert additional Control Caption columns to the right of the existing three.

Similar to the Command Bar Name column, the Control Caption columns can be used to create new controls or add subcontrols to existing controls. If the command bar builder code detects that the caption in the Control Caption column refers to an existing control on the current command bar, it will assume you want to add subcontrols to it. Otherwise it will create a new control with the specified caption using the settings specified in later columns.

Regardless of whether you are creating a single control or a cascading series of menus, each control must occupy its own row. The position of a control's caption within the series of Control Caption columns determines the level at which the control will be added. Look again at Figure 8-1. Notice that even though all three Control Caption columns have entries, no row has a Control Caption entry in more than one of the three columns. This is an absolute requirement.

You can provide an accelerator key for your control by placing an ampersand directly to the left of the character that you want to use as the accelerator key character. The control can then be activated from the keyboard by pressing the Alt key and the specified character simultaneously. This feature only applies to controls that display their caption and are currently visible.

NOTE

When a control displays a dialog, standard user interface conventions dictate that the caption of the control, if it displays one, should be followed by an ellipsis. See the Excel File > Print… menu for an example of this convention.

Position

The Position setting applies only to CommandBar objects. It specifies the position on the screen where the CommandBar will appear when it is displayed. This setting must be one of the following msoBarPosition enumeration members:

msoBarBottom
The command bar will be docked at the bottom of the screen.

msoBarFloating
The command bar will not be docked but instead will float over the screen.

msoBarLeft
The command bar will be docked on the left side of the screen.

msoBarPopup
This setting is used to specify command bars that will be displayed when the user right-clicks with the mouse. The command bar will be displayed at the position where the user right-clicked. Command bars with this position setting must be displayed in response to one of the BeforeRightClick event procedures using the syntax:

Application.CommandBars("Name").ShowPopup

msoBarRight
The command bar will be docked on the right side of the screen.

msoBarTop
The command bar will be docked at the top of the screen. This is the default value if no position is specified.

IsMenubar

The IsMenubar setting applies only to CommandBar objects. If set to True, the specified command bar will be the menu bar when it is visible. You can define multiple command bars as menu bars for different purposes, but only one menu bar can be visible at a time. If the IsMenubar setting is False, the command bar will be a toolbar or popup depending on the Position setting. The IsMenubar property must be False for command bars with the Position property value msoBarPopup or a runtime error will occur. Therefore, the command bar builder code will enforce this value for msoBarPopup command bars regardless of the value actually entered in the table. The default value for the IsMenubar setting is False.

Visible

The Visible setting applies only to CommandBar objects. If set to True, the specified CommandBar will be visible, subject to the following limitations:

If more than one command bar has both the IsMenubar and Visible settings set to True, the last such command bar in the table will be the menu bar that is actually displayed. All other menu bars will be hidden.

The Visible property does not apply to and has no effect on command bars with the Position value msoBarPopup.

The default value of the Visible setting is False. This enables you to create a large number of command bars when your application starts up and then display them on demand as needed.

Width

The Width setting applies to CommandBar and CommandBarControl objects. The Width setting must be a positive whole number greater than zero. This setting is not required and there is no default value. If the Width setting is not specified, the width of the command bar or control will be determined automatically by VBA. If the Width setting is not specified for a command bar, VBA will make the command bar wide enough to display all of the controls it contains on a single row. If the Width setting is not specified for a control, VBA will make the control wide enough to display its icon and/or caption.

For CommandBar objects, the Width setting applies only when the Position setting is msoBarFloating. The Width setting is ignored for all other command bar Position settings. You cannot make a command bar wider than its automatically calculated width. Setting the width of a floating command bar to a value narrower than its automatically calculated width enables you to stack controls in multiple rows rather than displaying a long, single-row command bar.

For CommandBarControl objects, the Width setting always applies and you can set it to any positive whole number greater than zero. If the specified width is too narrow to display the caption and/or icon of the control, however, it will be ignored. Note that all controls on the same popup menu list will have the width of the widest control in the list regardless of their individual Width settings.

There are no hard-and-fast rules for deciding exactly what the Width setting should be. The best approach is to first build your command bars and controls without specifying the Width setting. Then use the Immediate window to examine the width property that has been automatically assigned by VBA. You can use that as a starting point from which to increase or decrease the width of your command bars and/or controls.

Protection

The Protection setting applies only to CommandBar objects. This setting specifies what type of modifications the user will be allowed to make to the command bar. This setting must be one or more of the following msoBarProtection enumeration members. To apply multiple Protection values, just add the values together in the Protection cell for the command bar in question.

msoBarNoChangeDock
The user cannot change the position at which the command bar is docked.

msoBarNoChangeVisible
The user cannot change the visibility status of the command bar. If the command bar is visible, the user cannot hide it; if the command bar is hidden, the user cannot display it.

msoBarNoCustomize
The user cannot add or remove controls on the command bar.

msoBarNoHorizontalDock
The user cannot dock the command bar in any horizontal position, either top or bottom. Without any additional protection values, the command bar can still be docked vertically. To prevent a command bar from being docked anywhere, just add the msoBarNoHorizontalDock and the msoBarNoVerticalDock enumeration member values together in the Protection cell.

msoBarNoMove
The command bar cannot be moved. Be careful with this option because it will prevent the user from moving the command bar under any circumstances. For example, if you create a floating command bar whose width causes it to appear partially off-screen, the user will not be able to move the command bar into a position where they can access all of its controls.

msoBarNoProtection
The user can make any changes to the command bar that he wants.

msoBarNoResize
The user cannot modify the width or height of the command bar.

msoBarNoVerticalDock
The user cannot dock the command bar in any vertical position, either left or right.

There are two ways a user can delete your command bar regardless of its Protection setting, even if you have disabled the View > Toolbars > Customize menu. Both of these methods provide "back doors" to display the Customize dialog. It is particularly important to disable these options in dictator applications where deleting a custom command bar may leave the user with no way to properly exit the application.

First, if the Toolbar List command bar is enabled, the user will be able to delete your custom command bar by right-clicking anywhere over the command bar area and selecting Customize from the shortcut menu. To disable the Toolbar List command bar, execute the following line of code:

Application.CommandBars("Toolbar List").Enabled = False

Second, if any empty toolbar docking surface is exposed on screen (typically beyond the right side of a toolbar), the user can double-click anywhere within this area and the Customize dialog will display. There is no way to directly disable this feature, so you must indirectly disable it by ensuring that no uncovered toolbar docking area is left exposed by your application. The easiest way to do this is to add a nonfunctional CommandBarButton control (one with no Caption or OnAction assignment) at the end of each of your toolbars and set it to be wide enough so it will cover the entire toolbar docking area regardless of the user's screen resolution.

IsTemporary

The IsTemporary setting applies to CommandBar and Command BarControl objects. If set to False, the specified command bar or control will be persisted between Excel sessions. Setting this property to True causes the command bar or control to be discarded when the current session of Excel exits. The default value for this setting is True.

The command bar builder will rebuild all command bars and controls defined in the table each time your application runs, so the occasions when you want your custom command bars or controls to be persisted between Excel sessions are very rare. Leave this setting blank so the default value is used unless you have a very good reason to do otherwise.

IsEnabled

The IsEnabled setting applies to CommandBar and CommandBarControl objects. This setting determines whether the command bar or control is enabled on startup. A value of True causes the command bar or control to be enabled. A value of False causes the command bar or control to be disabled. Disabled command bars will not be visible to the user. The IsEnabled property overrides the Visible property for command bars in this respect. Disabled controls will be visible but will appear grayed out. The default value for this property is True.

OnAction

This setting applies to CommandBarControl objects. It holds the name of the procedure that will be run by the control. This procedure must be a public procedure located in a standard code module. If you want to trap the Click or Change event rather than assigning a procedure to the OnAction property, you can leave this setting blank. We cover control event trapping in the Hooking Command Bar Control Events section later in this chapter. If the Control ID setting is anything other than 1, the OnAction setting is ignored. You will understand why this is the case when we describe the Control ID setting next.

Control ID

This setting applies to CommandBarControl objects. Giving this setting a value of 1 means that you are creating a custom control whose properties are specified by the rest of the columns in the table. Any value other than 1 is interpreted as the ID of a built-in Excel control. In that case, the built-in control specified by the Control ID value will be added to your command bar, along with its function and appearance. If you specify a built-in control using the Control ID setting, the following command bar definition table settings will be ignored:

OnAction

Control Type

Control Style

Shortcut Text

State

ListRange

Lists

You can determine the ID you need to use in order to add a built-in control to your command bar in the following manner. Assume you want to add the Print menu item from the File menu on the Worksheet Menu Bar to your custom command bar. Enter the following into the VBE Immediate window:

? CommandBars("File").Controls("Print...").ID
4

The Immediate window is covered in more detail in Chapter 16 VBA Debugging, but for now note that the ? character tells the Immediate window to print the result of the expression that follows it. In this case, the result is the number 4, shown directly below the expression. This is the ID of the Print control. To add this control to your custom command bar, you just place 4 in the Control ID column of the appropriate row in the command bar definition table.

NOTE

A quirk in the Office CommandBars object model enables you to access the top-level menus of the Excel menu bar as CommandBar objects in their own right. If you loop the contents of the CommandBars collection, you won't find these menus contained in it, but you can access them using the syntax shown above just the same.

Control Type

This setting applies to CommandBarControl objects. It is used to specify what type of control you want. This setting must be one of the following msoControlType enumeration members:

msoControlButton
This is a menu or toolbar button that simply executes the specified OnAction procedure when it is clicked. The majority of CommandBarControls that you see on Excel's menus and toolbars are this type of control.

msoControlComboBox
This is a combo box control that enables users to either select an entry from a predefined list or enter a new value of their choosing. An example of this type of control is the Zoom combo box on the Standard toolbar. You can select from a predefined list of zoom values or supply your own.

msoControlDropdown
This control looks exactly like the msoControlComboBox control but the only option allowed is to select an item from the predefined list.

msoControlEdit
This is an edit box control that allows the user to enter an arbitrary text value.

msoControlPopup
This type of control is used to create a submenu containing a list of one or more menu items. All of the top-level menus on the Excel menu bar are of type msoControlPopup. Rather than doing anything directly, they just display their associated submenu. This is the only control type that can display a submenu.

For custom controls, the default value for this setting is msoControlButton. We examine how you use each of these control types in more detail in the Putting It All Together section later in this chapter.

NOTE

If you look in the VBE Object Browser, you will discover that there are anywhere from 21 to 27 different msoControlType enumeration members depending on the version of Excel you are using. Unfortunately, you are limited to one of the five members listed above when building custom CommandBarControls.

Controls with some of the other enumeration member types can be added to a custom command bar by adding a built-in control of those types (by adding the Borders button from the Formatting toolbar, for example, whose type is msoControlSplitButtonPopup). Some of the msoControlType enumeration members simply haven't been implemented. msoControlOCXDropdown is one example.

Control Style

This setting applies to CommandBarControl objects. It specifies the visual layout of the control. This setting does not apply to the Control Types msoControlEdit or msoControlPopup. It applies to the other control types in the following manner:

msoControlButton
Must be one of the following msoButtonStyle enumeration members:

msoButtonAutomatic
This is the default value for controls of type msoControlButton. For a menu item, this is equivalent to msoButtonIconAndCaption. For a toolbar button, this is equivalent to msoButtonIcon.

msoButtonCaption
This style displays only the caption assigned to the control. Any icon assigned to the control is ignored.

msoButtonIcon
This style is a bit confusing. It displays only the icon for toolbar buttons and only the caption for menu items. If no icon is specified for a toolbar button with this style, a blank button will be created.

msoButtonIconAndCaption
This style displays the icon and places the caption to the right of the icon for both menu items and toolbar buttons.

msoButtonIconAndCaptionBelow
This style has exactly the same effect as msoButtonIconAndCaption for menu items. For toolbar buttons it displays the caption centered below the icon.

msoButtonIconAndWrapCaption
This style is similar to the msoButtonIconAndCaption style, but it wraps long captions to the right of the icon instead of displaying them on a single line. This style gives very poor visual results when used with menu items, so we recommend against using it for that type of control.

msoButtonIconAndWrapCaptionBelow
For toolbar buttons this style is similar to the msoButtonIconAndCaptionBelow style except that long captions are wrapped beneath the button icon. For menu items this style gives exactly the same poor results as the msoButtonIconAndWrapCaption style, so we recommend against using it for that type of control.

msoButtonWrapCaption
This style is similar to the msoButtonCaption style in that it ignores any icon assigned to the control. The difference is that it wraps long captions rather than displaying them on a single line.

msoControlComboBox and msoControlDropdown
Must be one of the following msoComboStyle enumeration members:

msoComboNormal
This is the default value for controls of type msoControlComboBox and msoControlDropdown. It simply displays the control with no caption.

msoComboLabel
This style displays the caption directly to the left of the combo box or drop-down control.

Because the Control Style setting does not apply to controls of type msoControlPopup, you cannot modify the default appearance of this type of control. There is no workaround for this limitation. Because the Control Style setting does not apply to controls of type msoControlEdit, you cannot provide a caption for these controls. If your edit box control is located on a toolbar, you can work around this limitation by adding a nonfunctional msoControlButton with the msoButtonCaption style that displays the caption you desire directly to the left of the edit box control. We demonstrate this workaround in the Putting It All Together section later in this chapter.

Face ID

This setting applies to CommandBarControl objects. It specifies the icon that will be associated with the control. The Face ID setting can be specified in one of three ways:Putting It All Together section later in the chapter.

When operating under Excel 2002 or higher, you have the option of specifying an icon and a mask. This method provides significantly superior visual results for these versions of Excel. Both the icon and the mask must be 16x16 pixel graphics that are located on the wksCommandBars worksheet. The icon and mask picture names must be entered together into the Face ID cell separated by a forward slash (/) character. We discuss this method in more detail in the Loading Custom Icons from Files and Hooking Command Bar Control Events sections later in the chapter.

Note that the icon picture from the icon/mask pair will automatically be used in versions of Excel earlier than Excel 2002, making method three equivalent to method two when running on down-level versions of Excel.

The most important characteristic of a custom icon is that its background appear transparent when applied to a control. To use custom icons under Excel 97/2000, you must use the Set Transparent Color control from the Picture toolbar to specify a transparent background color for the single picture that will become the custom icon for your control. This method is illustrated by the before and after pictures shown in Figure 8-4 and Figure 8-5.

Figure 8-4. An Icon Picture Before Setting the Transparent Background

Figure 8-5. An Icon Picture After Setting the Transparent Background

Just place the Set Transparent Color control cursor over the color in your picture that you want to become transparent and click. That color will disappear and the background will show through it.

We have demonstrated this example with a picture that has been enlarged enough so that you can see what is happening. This is typically the way you would perform this operation in the real world as well. A 16x16 picture is too small to accurately point at the desired background color when you are dealing with a complex icon. Just stretch the picture out to a size large enough so that you can see what you are doing, set the transparent background color, then revert the picture to its original size in the following manner:

Excel 97/2000, Excel 2002 and Excel 2003 all use different UI drawing techniques, so picture backgrounds set to be transparent in one version of Excel will not appear transparent in other versions. As you will see in the section on Loading Custom Icons from Files later in the chapter, two new methods were added to the CommandBarButton object in Excel 2002 that enable you to load pictures directly into the control in a manner that is independent of their appearance on the worksheet.

For this reason we recommend that you use the icon/mask method described in point 3 above for creating custom icons in Excel 2002 and higher, while setting the transparent background color of the icon picture using Excel 97/2000. This gives the command bar builder an appropriately formatted icon for use in Excel 97/2000. It will automatically use method two under these versions of Excel. You do not have to do anything special to the mask picture, because the mask is not used under Excel 97/2000.

Begin Group

This setting applies to CommandBarControl objects. It is a True/False setting that specifies whether a separator bar will be placed above or to the left of the specified control depending on context. A value of True adds a separator bar. False is the default value for this setting; so if False is specified or this setting is left blank, no separator bar will be created.

Before

This setting applies to CommandBarControl objects. It is used to position a new control before an existing control. If the controls are arranged horizontally, the Before setting will place your control to the left of the control specified. If the controls are arranged vertically, the Before setting will place your control above the control specified.

The existing control can be specified either by its name or ID number. This setting is not required and there is no default value. If this setting is left blank or if the control specified by this setting cannot be located, this setting will be ignored. In this case, the control will be added at the system default position, which is at the end of the current set of controls on the same level. The Before setting is typically used to position controls that are being added to built-in Excel command bars.

Tooltip

This setting applies to CommandBarControl objects. It specifies the text that will be contained in the yellow tooltip message that displays when the user hovers the mouse pointer over the control. This setting does not apply to controls of type msoControlPopup or to any child control of an msoControlPopup control. If the Tooltip setting is not specified, the system default value displayed in the tooltip is the caption of the control.

Shortcut Text

This setting applies to CommandBarControl objects. It displays the keyboard shortcut that will be assigned to the control directly to the right of the caption for the control. This setting applies only to controls of type msoControlButton that are child controls of an msoControlPopup control (that is, menu items on a submenu). Specifying this setting does not actually assign the specified keyboard shortcut to the macro assigned to the OnAction setting of the control. You must do this separately in your own code. The Shortcut Text setting is not required and there is no default value.

Tag

This setting applies to CommandBarControl objects. It enables you to store String data for your own use. The Tag setting does not modify the appearance or function of the control in any way. One common use of this setting is to differentiate among controls that have been assigned the same OnAction procedure.

Assume you have assigned the OnAction settings of three controls to the same procedure. You then assign the values 1, 2 and 3 to the Tag settings of the first, second and third controls, respectively. When the specified OnAction procedure is called by one of these controls, you can identify the control that called it in the manner shown in Listing 8-1 and conditionally redirect program execution based on the result.

Listing 8-1. Distinguishing Controls Using the Tag Setting

Public Sub MyProcedure()
Dim lControl As Long
' Retrieve the Tag value of the control that
' called this procedure.
lControl = CLng(CommandBars.ActionControl.Tag)
Select Case lControl
Case 1
' Perform the action for control 1.
Case 2
' Perform the action for control 2.
Case 3
' Perform the action for control 3.
End Select
End Sub

As you will see in the Hooking Command Bar Control Events section later in the chapter, the Tag setting is also used to specify the custom controls whose events you want to hook as a group.

Parameter

This setting applies to CommandBarControl objects. It is functionally identical to the Tag setting. It is a place for the programmer to store String data that will not have any effect on the appearance or function of the control.

State

This setting applies to CommandBarControl objects. It enables you to create checked menu items or depressed toolbar buttons. The value for this setting must be one of the following msoButtonState enumeration members:

msoButtonDown
For toolbar buttons this creates the visual effect of the button being depressed. For menu items, the effect depends on whether there is an icon displayed with the menu item. For menu items with icons, the icon will appear depressed in a fashion very similar to the effect on toolbar buttons. For menu items without an icon, a depressed check mark will be added to the left of the menu caption.

msoButtonMixed
For all current versions of Excel, this value is indistinguishable from msoButtonDown. It is included in the command bar builder in case it becomes supported for some different purpose in a future version of Excel.

msoButtonUp
This is the default value for this setting. A State value of msoButtonUp has no effect on the appearance of newly created controls. This value only comes into play as a way to remove the effect of the msoButtonDown or msoButtonMixed values.

The State setting applies only to custom controls of type msoControlButton. Keep in mind that the State property is a dynamic property of the control. The command bar builder will create the control with whatever initial State value you specify, but after the control has been created you will need to write custom code to modify the State property appropriately in response to user actions. We demonstrate this in the Putting It All Together section later in the chapter.

ListRange and Lists

These settings apply to CommandBarControl objects of type msoControlComboBox or msoControlDropdown. We discuss these settings together because they are, in effect, a single setting that specifies the list to be loaded into a combo box or drop-down control. The purpose of these settings is as follows:

ListRange
This value specifies the address of the range on the wksCommandBars worksheet that holds the list to be loaded into the control. The specified range must be located in the Lists column. Like all other settings described so far, the ListRange setting must be located on the same row as the control to which it applies.

Lists
This setting is a list of values that will be loaded into the control. This is the only setting that does not have to be located on the same row as the control it applies to, and as we discuss below, it should not be located in rows that are part of any command bar or control definition.

You should always place your lists below the last command bar definition in the table. By doing this, if you need to insert or delete rows in a command bar definition, you will not inadvertently alter one of your lists. For similar reasons, use a dynamic formula to create the list address value for the ListRange setting. If you hard-code the list address and then insert or delete rows in the command bar definition table, the list address will no longer be valid. Use of a dynamically adjusting formula to specify the list location solves this problem. An example of a dynamically adjusting List address formula is shown here:

=ADDRESS(ROW(Y48),COLUMN(Y48))&":"&ADDRESS(ROW(Y53),COLUMN(Y53))

This formula indicates that the Lists setting is located in column Y of the command bar definition worksheet. The list currently starts in row 48 of that column and ends in row 53 of that column. Notice, however, that this formula uses relative addresses. If you insert or delete rows above the list, the addresses specified in the formula will automatically adjust to the new location of the list. The result of this formula, as displayed in the ListRange cell and as read by the command bar builder code, is shown here:

$Y$48:$Y$53

The command bar builder will use the range specified by this address as the list to be loaded into the control defined in the row in which the ListRange setting is located. Note that the last command bar definition in this example ends at row 47. Therefore, placing the list below row 47 ensures that any subsequent insertions or deletions within the command bar definition table will not have any effect on the contents of the list. We demonstrate this technique in the Putting It All Together section later in the chapter.

Post Mortem

Although it seems as if every command bar and control property under the sun has been covered here, we're not even close. Only the most frequently used properties have been included in the command bar builder. Other properties that you may find an occasional need for can always be manually coded into your application.

Examples of properties that are not included in the command bar builder are the Height and RowIndex properties of the CommandBar object, because in our experience these properties are rarely used when building command bars. Properties of the CommandBarControl object that are not supported include the DescriptionText property, because it simply duplicates the purpose of the Tag and/or Parameter properties and the HyperLinkType property, because it is so rarely used.

There are also dynamic properties such as IsEnabled and State whose initial values are set by the command bar builder, but whose subsequent values must be managed by custom code in your application as the need to change them arises.