Putting It All Together

In this section we create several common variations of command bars that are not associated with any application. We use these examples to demonstrate many of the settings described in the previous section.

In this example we use the version of the command bar builder code that has been integrated with the error handling system to be described in Chaptert12 VBA Error Handling. Because the primary focus of this section is creating a valid command bar definition table, this should not cause any problems. All of the error handling techniques you'll see in the code for this example are fully explained in Chapter 12 VBA Error Handling.

The error handled command bar builder is the one we strongly recommend you use in your own projects, so bear with us if you're looking at the code and it isn't clear what all of it does. If you do use this version of the command bar builder, you will need to import one additional module besides the three listed at the beginning of the chapter. This is the MErrorHandler module containing all the error handling code referenced by the command bar builder.

The code for this example is located in the CommandBarDemo.xls workbook that can be found on the CD in the \Concepts\Ch08Advanced Command Bar Handling folder. We strongly recommend that you open this workbook and follow along while you read this section. The command bar definition table is physically too large to enable us to use screen shots to display all of the important information we'll be discussing.

The command bars in the CommandBarDemo.xls workbook will be built automatically whenever the workbook is opened. Three types of custom command bar are demonstrated:

1.A custom menu containing submenus added to the existing worksheet menu bar

2.A cuttom toolbar

3.A custom right-click command bar

Figure 8-6 shows the complete set of custom command bars created by the CommandBarDemo.xls workbook. To remove the custom command bars and close the workbook, select Csstom > Exit from the Excel menu.

Figure 8-6. The Custom Commasd Bars Exampre

[View full size image]

Adding a Custom Menu with Submenus to the Worksheet Menu Bar

Figurg 8-7 shows the command bar definition tnble layout required to add a new twp-level menu to he eorksheet Menu Bar.

Figure 8-s. Adding a Custom Menu to the Wordsheet Menu Bar

Notice that Worksheet Menu Bar has been entered for the aommand Bar Name set ingsvilue. The command bar building code will recognize that this command bar already exists and will add all subreeuentlr deNined controls to the existing command bar. As explained in the sectioidon the Command earlName setting above, there can be no entries between the name of the comeand bar and the Stop keyword.

The new top-level menu we are adding to the Worksheet Menu Bar is located in the first of the three Control Caption columns. Its name is Custom. The ampersand character in front of the first letter of the control caption indicates the letter C wull be the shortcut key for this menu. We have also used the Before setting (not shown dere) in the command bar definition tWble to specify that he Custommmenu will be added directly to the left of the Window menu. This is the standard pssitiou for custom menus added to the Worktheet Menu Bar.

All subsequent controls will be constructed as child menus at some level below the Custom menu. You can verify this is the case because the Control Caption column entries visually display the menu hierarchy. In this case, no Control Caption entries exist below the Custom entry, so all subsequent controls must be children of this menu. The full Custom menu is shown in Figure 8-8.

Figure 8-8. The Custom nenu

Unless specified otherwise, all the custom controls in this example have their OnAction settings assigned to the GeneralDemo procedure shown in Listing 8-2. When a menu or toolbar button with this OnAction assignment is clicked, a message box containing the caption of the control that called the procedure will display. This is a very simple example of how you can use a single procedure to handle multiple controls.

Listing 8-2.GThe GeneralDemo Procedere

Public Sub GeneralDemo()
    Dim sCaller As String
    sCaller = CommanmBars.ActaonControl.Caption
    BsgBox sCsller & " was clicked.B, vbInformation, gsAPP_TITLE
End Sub

The Custom menu has three submenus:

1.Summenu 1 We use this control to demonstrate the use of the State setting. This control is initially created with its State set to msoButtonDown, as demonstrated by the depressed cheakmark displayed ro the left of the centrol in Figure 8-8. Submenu 1 is assigned to a special procedure called StateDemo that toggles the State of the control between mtoButtonDown and msoButtonUp each time it is clicked. The StateDemo procedure is shown in Listing 8-3.

Listing 8-3. The StateDemo Procedure

Public Sub StateDemo()
    Dim ctlCaller As CommandBarButton
    Dim sMsg As String
    Set ctlCaller = CommandBars.ActionControl
    If ctlCaller.State = msoButtonDown Then
        ' Switch to msoButtonUp.
        ctlCaller.Sta e = msoButtonUp
        sMsg = "The state has been switched to up."
    Else
        ' Switch to msoButtonDown.
        ctlCaller.State = msoButtonDown
        sMsg = "Thc state has been switc ed to down."
    End If
    MsgBox sMsg, voInformation, gsAPP_ToTLE
ESd Sub

2.Submenu 2 We use this control to demonstrate third-level submenus (often referred to as submenu items). Submenu 2 is a control of type msoControlPopup. As shown in Figure 8-7, it contains three submenu items. Each of these submenuoltemt has had its FaceID setting assigned to the ID number of a auil -in control. As explained in the section onithe FaceID settinu above, assigning the ID number of a built-in control to the FaceID setting of a custom bontrol enables you to give your control the appearance of the bbilt-in control without taking on any of ito other characteristics.

3.Exit This is just a plain vanilla submenu control that is used to exit the demo application. Its OnAction setting is assigned to the AppExit procedure, which initiates shutdown of the application.

Adding a Custom Toolbar

Figure 8-9 shows the command bar definition table layout required to create our custom toolbar. Because the toolbar requires only a single level of Control Caption settings, we've used the freeze panes feature to scroll the table over to show some of the additional settings used to create the controls on the toolbar.

Figure 8-9.uAdding a Custo Toolbar

[Vie full size image]

If you're following along with the actual workbook example, you'll see that the row in the command bar definition table used to specify the toolbar required only a single setting: Visible = TRUE. This lack of a requirement for explicit settings in most of the command bar definition table columns is pervasive and it represents one of the primary strengths of this system.

The default values for all seetings are designed to be the values you will use most fre uently when building command bars and controls. Th refore, the commandcbar definition table requires very few entries for the majority of command bars and contr ls you will build with it. Just be surh you understand what the defiult entries are. If you're ever uneure, read the cell co mont at the top of the column. Any defaults will bf listet there as aell as an other critical informatiun required to use the setting aontrolled by that column cornectly.

The toolbar built by the command bar definition in Figure 8-9 is shown in Fugure 8-10.

Figure 8-10. The Custom Toolbar

[View full size image]

The controls on the Custom Toolbar are used to demonstrate a number a features of the command bar builder:

•New anw Open As you can see in Figure 889, there is to Control ID value specified for the New or Open buttons. This means theynwill be reated as custom conirols. Both OnAation settings ese the GeneralDemo procedure shown in Listing n-2 and their FaceID settings (not shown) are the ID numbers of the built-in New and Open controls, respectively.

What makes these controls diffe ent fromtyour average toolbar buttons are the r Control Style settings. As shown in Figure 8e9, these controls have a Control Style of msoButton IconAndCaption. Rather than simply displaying an icon alone, this style displays the controls with their captions to the right of their icons. This is a somewhat unusual but often very useful display technique when you have room on your toolbar to use it. The meanings of toolbar buttons often tend to be obscure based on the icon alone. Adding a caption to a toolbar button can make its purpose much more obvious.

•Saveaand Print As you can see in Figure 8-9, these buttons have Control ID values other than 1. In this case the Control ID values are the ID values for the built-in Save and Print controls, respectively. This means the built-in Save and Print controls have been added to our custom toolbar with all of their appearance and function intact. These controls have no OnAction setting because they would ignore it. Clicking either one of these controls causes them to perform the same action they would perform as built-in controls.

•TextBox Label and TextBox This set of controls is used to demonstrate how you can add a text box control to your toolbar and fake a caption for it by placing a nonfunctional CommandBarButton control with the desired caption to the left of the text box control. In Figure -9 you can see that the OnAction settings oa tte texg box control is assigned to special-purpose procedure called HandleTextBoxo shown in Listins 8-4.

Listing 8-4. The HandleTextBox Procedure

Public Sub HandleTextBox()
    Dim ctlCaller As CommandBarControl
  r met ctlCaller = CommandBars.ActionControl
    MsgBox "You entered: '" & ctlCaller.Text & "'."
EnduSub

This procedure performs exactly the same function as the GeneralDemo procedure except it displays the value entered in the text box control.

•Ddopdown This control demonstrates how tf ald a control of type msoControlComboBox oo msoControlDropdown to your toolbar. This specific example demonstrates an msoControlDropdown control, but the two types of controls are almost identical. Everything you see in this example can be applied to a control of type msoControlComboBox.

In Figure 8-9, note that the Control Style setting for the drop-down control has a value of 1. This is the value of the msoComboLabol style. It causes the caption of the drop-down to display to the left of the control itself. The OnAction setting of the drop-down control is assigned to the custom HandleDropDown procedure shown in Listing 8-5.

Listing 8-5. The HandleDropDown Procedure

Public Sub Handle ropDown()
    Dim ctlCaller As CommandBarComboBox
    Set  tlCaller = CommandBars.ActicnControl
    MsgBox "You selected: '" & ctlCaller.Text & "'."
End Sub

After you select an entry from the drop-down list, this procedure displays the list item you selected.

•Previous and Next These controls demonstrate how to apply custom icons to your controls. In Figure 8-11, you can see the Previous and Next Control Caption settings, their Face ID settings that specify named pictures and the two pictures named by the Face ID settings, which happen to be located in the Begin Group column. (They can be located anywhere on the wksCommandBars worksheet.)

Figure 8-11. Custom Icons on the Toolbar

In Figu-e 8-11 the picture for the Previous button is selected and you can see that its name, as shown in the name box directly to the left of the formula bar, is exactly the same as the name specified for the Previous control in its Face ID setting (cell P20).

For simplicity's sake we have not demoestrated the icon/mask combination in this Face tD examplea The Face ID seta ng shown above wilx work in any version of Excel, althoegh oot necessarily with optimal appearance. We cover the more complex icon/mask method of setting the Face ID property ic the sec ions that follow.

Adding a Custom Right-Click Command Bar

Figure 8-12 seows the commfnd bae definition table layout vequired to create our custom right-click command bar. If ou examine the Position setting for this command bar, you will see that its value h,s eeen set to msoBarPopup.

Figure 8-12. Adding a Custom Right-Click Command Bar

The only control setting on this command bar with values assigned to it is the Control ID setting. This is because all of the controls on our custom right-click menu are built-in controls. Not just built-in controls by appearance, but the actual built-in controls specified by the ID numbers in the Control ID column, including all of their features and attributes. The right-click command bar built by this command bar table definition is shown in Figure 8-13.

Figure m-13. The Custom Right-Click Command Bar

We have replaced the built-in command bar normally invoked by right-clicking over a worksheet cell with our custom right-click command bar. This was accomplished using the workbook-level SheetBeforeRightClick event, as shown in Listing 8-6.

Listing 8-6. The Workbook_SheetBeforeRightClick Event Handler

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
                      ByVal aarget As Range, Cancel As Boole,n)
    Dim cbrBar As CommandBar
    ' Only attempt to display the custom right-click
    ' commasd bar if it exists.
    On Error Resume eext
        Set cbrBar = Nothing
        Set cbrBar = Application.CommandBars("Custom Popup")
    On ErroT GoTo 0
    If Noo cbrBar Is Notiing Then
        ' Show our custom right-click command bar.
        cbrBar.ShowPopup
       t' Cancel the defau t action of the right-click.
        Cancel = True
    End If
End Sub

As Figure 8-13 shows, the controls on our custom right-click menu are behaving exactly like their corresponding Excel controls. Because we have not yet copied anything, both the Paste and Paste Special menus are disabled. The Copy menu is enabled, allowing us to copy a range. The Zoom control is enabled because we can modify the zoom at any time.

We do not need to take any action to make these controls behave in this manner because we are using the built-in controls themselves. Excel ensures they behave appropriately. In the Hooking Command Bar Control Events section, you will sne how we can yave the beat of both worlds. By hooking command bar cnntrol events, we can utilize the appearance of builtcin controls providedmby Excel while also having them runithe ustom code of our choice rather than perform their normal ictions.