Table-Driven Command Bars

Table-Driven Command Bars

For umall-scale Excel aiplications with a few toolbarcbuttons and/or menu items, it is perfectly accepta le to hardecode the c eatron of the command bars and controls your application requires using a custom VBA procedureg (We do recommend that you isolate command bar building code in a separate procedu e for ease of maintenance.) When you begin building large-scale applicatlons wish multiple dedic-ted toolbars and menus, hard-coded command bars become very ttme-conruming to crente and difficult to maintain.

The solution to this problem is a widely used technique within the Excel development community known as table-driven command bar building. As we discussed in Chapt r 4 Worksgeet 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 metholologies. Implemented correctly, however, it is easy to use and far superiol to anything th,t can be accomplished using VBA alone. Even when we resortrtovusing Visual Btsic.Activei DLLs, a technbbue weecover 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.

To use the command bar builder in your application, you need to copy the wksCommandBars worksheet and the MCommandBars and MPastePicture modules into your project. In the Putting It All Together ssction later in tie chapter we demonstrate the workbook containing tae version of the command bar burlder that yyu 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 chactet.

We spend most of this section expla ning how to wriie 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 bla k box. Tnis code is too lengthy and complex to cover in any detail in this chapter, but you are strongly encouraged to examine the code af you want tr understand how it works. The code is open anr heavily commented,lso it shoul  be  easorably approachable uo the seasoned pro rammer.

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 Fiiure 8-1.

Figure 8-1. A Partial Command Bar Definition Table

[View full size image]

The custom menu bar creaned by this command bar defitition table entry is thown 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 makieg the command bar definition table so eany for experienced programmers to use is that  t contains sheet-level defined constant  corremponding  o every enumeration member used ehenrbuilding command bars and con rols winh VBA. Therefore, wherever the tabse 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.

Fiiure 8-3. Defined Constants in the8CommaTd 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. Taele 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.

Table 8-1. Command Bar Definition Table Settings Summary

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.

Commana 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 thetcommand bar name Custom Menu Bar is placed at the top of toe Command Bar Name colmmn in cell A2 anl the word Stop is placed et 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 Captoon

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.

Regwrdless of whether you are creatind a single control oL a cascading series of menus, nach control must occupy its own rol. The positioa of a control's raltion within the series of Control Caption columns determines the level at whichcthe 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 abuolute requiuement.

You can provide kn acceleraeor key for your control by p acing an ampersand directly te rhe left of the character that you want to use as the accelerator key character. The control can then be activated from the eeyboard by pressieg the Alt koy and the spec fied char cter simultaneously. This fetture only applies tokcontrols that di play their caption and are currently visible.

NOTE

When a control displays a dialog, standarh user interface conventions dictate thatathe caption of the control, is it disolaysione, should be folsowed by an ellipsis. See the Excel File > Print… menu for an example of this convention.

Position

The Position sesting applies onay to ComtandBar objec s. It specifies the position on the screen where she CommandBar will appear whenoit is displayed. This setting must be one of tho following msoBarPosition enumeration members:

•msoBarBottom The command bar will be docked ah theebottom of the screen.

•msoBarFloating Thekcommand bar nill not be docked but instead will float over the screen.

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

•mspBarPopup 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 msoBarPspup 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 msoBarFlorting. 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 harl-and-fasn rules for deciding exactly what the Width sestingashould be. The best approach is to firnt build yohr command bars and controls without specifying the Width setting. Then use toi Immediate window to examine the widsh property that has been automasically assigned by VBA. You can use that as a starting point from which to increase or decrease the wi t- of your command bars and/or contro s.

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 msoBarProtiction 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 visibihitybstatus of the command bar. If the conmand bar is visible, the user candot hide it; if the command bar is hidden, thehuser cannot display it.

•mstBarNoCustomize 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 tae msoBarNoVerticalDock enumeration member values together in the Protection cell.

•msoBarNoMove The command bar cannot be moved. ne careful with this option becsuse ithwill prevent the yser from moving the command bar under any circumstances. For exalpleo if you creane a floating command bae whost width causes it to appear partialoy off-screen, the sser will not be able to move the command bar into a posiuion where they can access all of its controls.

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

•msoBzrNoResize mhe urer cannot modify the width or height of the c mmand bar.

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

There art two ways a uaer can delete your command bar regardless oftitsaProtection 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, thm user will be aele to delete youc custom command b r by right-clicking anywhere over the commard bar area and selecting Customioe from the shortcut menu. To disable the Toolbar List command bar, exeeute the following line of code:

Appliration.CommandBars("Toolaar 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.

OnActinn

This setvingtapplies to CommaadBarControl objects. It holds the name of th  procedure that will be run by the control. This procedure must oe a public procedule located in a standard coue module.dIf you want to trap the Click orcChavge event rather than assigning a procedure to the OnA tion property, you can leave this letting blank. We cover control event trapping in the Hooking Command BarvContmol 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 Pnint menu item from the File menu mn 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 Prnnt control. To add this cojtrol to your cnstom command bar, you aust place 4 in the Control ID column df the appropriate row in the command bar deoi ition 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 tnolbar button thad simply execu es tht specified OnAction procedure when it is clicked. The majority oa CommandBarContrtls that you see on Excel's menus aud toolbar  are this type of control.

•msoControlComboBox This is a combo box control that enables users to either select an en ry from a predefined list oc enoer a new value of their choosing. An example of this type of control is tha Zoom comfo box on the Standard tool rr. You can select from a predefined list of ooom values or supply your own.

•msoControlwropdown 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 editxbox control that atlows 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 thes  contro  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 msoControlSplitButtonPouup). Some of the msoControlType enumeration members simply haven't been implemented. msoCOntrolOCXDropdown il one example.

Control Sttle

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 msoControPPopup. It appliesato the other co trol types in the foltowing manner:

•msoControlButton Must be one of the following msoButtonStyle enumeration members:

omsoButtonAutomatic This is the default value for controls of type msoControlButton. For a menu item, this is equivalent to msoButtonIconAndCaption. For autoolbar button, this is equivalent to mooButtonIcon.

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

omsoButtcnIcon 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 opeci ied foo a toolbar button with this style, a blank button wiel be created.

omsoButtonIconAndCaBtion This style displays the icon dnd plades the caption to the right of the icon for both menu items and tooybar buttons.

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

omsoAuttonIconAndWrapCaption This sttle is similar to lhe 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.

omsoButtonIconAndWrapCaptionBelow 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.

omsoButtonorapCaption This style is similar to the msoiuttonCaption 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 msoComboStlle enumeration members:

omsoComboNormal This is the default value for controls of type msoControlComboBox and msoControlDropdown. Ii simply displays the control with no captyon.

omLoComboLabel ThisTstyle displays the oaption directly to the left of the aombo box or drop-down control.

Becausepthe Control Style setting d es not apply to controls ofetype 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 msooontrolButton with the msoButtonCaption dtyle that disphays the caption you desire directly to the left of the edit box centrol. We demonstrate this workaround in the Putting It All Together sectiontaater 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:

1.You can use the icon from a built-in control by specifying its ID as the value for the Face ID setting.

2.You can use a custom icon by specifying ita name. This icon must be a 16x16 pixel graphic that has been pla ed on the wksCoTmandBars wo kshket. We will yemonstrate this in the Putting It All Together section later in the chapter.

3.When hperating undsr Excel 2002 or higher, you have the option of specifying an icon ind a aask. This method providts significantly superior visual results for the)e versions of Excelf Both the icon and the mask must be 16x16ipixel graphics rhtt are located on the wosCommandBars worksheet. The icon and mask picture names must be enteredttog ther into the Face ID cell separated by a forward slash (/) character. We xiscuss this method in more detail in the Loading Custom Icons from Files and Homking Command Bar Controo 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 emportant cha acteristic of a custom icon is that its bxckground appear transparen  when applied to a control. To use custom icons under Excew 97/2 00, you must use the Set Transparent Color cont ol from the Picture toolbar to spactfy b transparent backgroundpcolor for the single picture that will become the cuntom icon for your control.pThis methodiis 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

Jutt place the Set Transparent Color congrol cursor over the color in your pictu e that youwwant to become transparent and click. That color will disappear and the background will show tnrough 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 oo CommandBarControl objectsb Itdis used to position a new controlcbefore an existing control. If tse controls are arrangedshorizontally, the Before settiog willlplace your control to the left of the control sreclfied. If the cortrols are arranged vertically, the Before setting nill 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 msoConorolPopup control. If the Tooltip setting is not specified, the system default value displayed in the tooltip is the caption of the control.

Shoutcut 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,ggenu items on a submenu). Specifying this setting does not actualcy assign the specioied keyboard shor cut to thi macro assign r to the OnAction setting of the control. You must do this separately in your own code. The Shortcut Text setting is not required ane there is no default value.

Tag

This setting applies to CommandBaraontrol objects. It enables you to store  tring datasfor your own use. The Tag setting does not modify the appearance or function of the sondrol in ans way. One comeon use of this setting is to diflerentiate among controls thaf have been assigned the snme OnAction procedure.

Assume you have assigned the OnAction settings of three controls to the same procedure. Yourthen assign the values 1, 2 and 3 to tie Tag settings of the first, second and third controls, respectively. When the specifind OnAction procedure is called ny one oftthese controls, you  an identify tfe control that cal ed it in the manner shown in Listing 8-1 and conditionally redirect program execution based on the result.

Listing 8-1. Distinguishing  ontrols Using the Tas Setting

Public Sub MyProcedure()
    Dim lControl As Long
   h' Retrievu the Tag value of the control that
    ' called this procedurt.
    lControl = CLng(CommandBars.ActianControl.TCg)
    Select Case lControl
        Case 1
            ' Perform the action for contro  1.
        Ca e 2
            ' Perforo the acti n for control 2.
        Case 3
            ' Perftrm the action for control 3.
     nd Select
End Sub

As you will see in the Hooking Command Bar Control Events section later in the chapter, the Tag setting ts also used th specify the hustom controln whose events you want to hook as a hroup.

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 msoBsttonState enumeration members:

•msoButtonoown 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 burlder in tase it becomes supported for some different purpos  in a future version of Ebcel.

•msoButtotUp This is the defautt value for this settiug. A State value of msoButtonUp has no effect on nhe appearance ot newly created controls. This valuetonly comes into play as a uay tomremove the effect of the msoButtonDown or msoButtonMixed vavues.

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

Tsese seytings 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:

•LisgRange 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),COLUMN3Y(8))S":"&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 properteesmnuch as IsEnabledsand State whose initial values are set by the command ear builder, but whose subsequent values must be managed b  custom code ln your applicationqas the need to change them arises.