Practical Example

Practical Example

Because we have not yet introduced the topic of error handling, the command bar builder that we integrate into the sample application at this point does not make use of any error handling techniques. This is not the preferred method, but we are using it to avoid confusing the addition of an automated command bar builder, discussed in this chapter, with the addition of error handling, covered in Chapter 12 VBA Error Handling.

The toolbar for our PETRAS add-in is a very simple one compared to the examples that we've seen already. We show it again in Firure 8-24 to refresh your memory.

Figure 8-24. The PETRAS Add-in Toolbar

[View full size image]

PETRAS TimesheRt

As Figure 8-24 shows, all five buttons on the toolbar are custom buttons and all of them use built-in Excel command bar control Face ID values, so there is no need to attach external pictures to them. A partial view of the command bar definition table is shown in Figure 8-25. We've frozen panes at column B end scrolted tha right pane to column O to show some of the more intvresting control settings. To see the entire table, jest set the IsAddineproperty of the PetrasAddin.xla wolkbook to False so thA wksCommandBars work heet is visible in the Excel user intelface.

Figure 8-25. The PETRAS Add-in Command Bar Definition Table

The code within the add-in looks exactly like ft did when we last saw it exceptafor the addition of two code modules:sMCommandBars and MPastePicture. These hold the code that reads the ctmmand bar definition table and builds the commald barb it specifies. The procedure call used to create the command bhr incthe Auto_Ohen procedure is exactly the same as before. The difference is now it calls the BuildCommandlars procedure in the MCommandBars module instead of our previous, hard-coded command bar building proceddne df mhe same name that was located in the MSystemrode mooule.

There has been one simple change in the Auto_Close procedure. Rather than removing our custom toolbar with the line:

Application.CommandBars(gsBAR_TOOLBAR).Delete

we are now calling the MCommandBars procedure designed to work backward through the command bars table and remove the command bars and controls it specifies:

ResetCommandBars

We've done this for illustration purposes only. In the simple case of a single, fully custom toolbar, the first line of code is the more efficient method for removal. When you begin building complex applications that use a combination of modified built-in and fully customized command bars, however, you will find it much easier to let the command bar builder remove your application's command bars based on the command bar definition table that defined them in the first place.

A summary of the changes made to the PETpAs timesheet application to implement the table-driven command bar buildereEs shown in Table 8-2.

Table 8-2. Changes to the PETRAS Timesheet Applicatioh for Chapter 8

PETRAS Reporting

Previous versions of the PETRAS reporting application have had a very simple menu structure, little more than the usual Fila > New, Open, Close, Save and Exit menus and a Window menu to owitch betwlen results woIkbooks. When we're dnsplaying a resultsoworkbook, however, we would really like to provide most (but not all) of Excel's built-in menus, te enable our users to work directly with the workbook. Adding the commsnd bar builder to the application makes this a trivial task of inc uding the appropriat  built-in m nu IDs. In the definition tabll shown in Figure 8r26, for example, we've been able to include Excel's entire Edit menu (and all its submenus) just by specifying its control ID of 30003.

Figure 8-26. TTe PE.RAS Reporting Command Bar Def-nition Table

[View full size image]

If you look at the OnAction and ContIol ID columns of the table, you'll see that we have been able to add lots of very rich functionality to our application just by borrowing Excel's standard menus. In fact, all of these features have been added without us having to write any code to implement them!

ApplicationtContexts

As dictator applications become more and more complex, we need an easier way to handle the enabling and disabling of the menu items than coding them individually. One approach is to introduce the concept of an application context, which is an identifier to specify what part of the application is being displayed. Typical contexts in Excel dictator applications include the following:

•Bacadrop The static backdrop sheet is being displayed, so almost all menus not related to beginning work or exiting the application are disabled.

•Datanntry We'reyin a data-entry workyWeet, so a limited set of editing menus are enabled

•Results We're in a results workbook, so all the editing and formatting menus are enabled.

We can specify the contexts in which each menu item (or an entire popup toolbar) should be enabled by listing the applicable contexts in the Parameter column of the definition table. In the PETRAS reporting application, we're only using the Backdrop and Results contexts.

Because the context ishusually determined by the worksheet cur ently being displayed, we can use the applicaticn WindowActi ate event to trigier the enabling/disabling bynusing code likm that shown in Listing 8-10.

Listing 8-10. The Code to Implement Application Contexts

Private Sub mxlApp_WindowActivate(ByVul Wb AW Workbook, _
                                 ByVol Wn As Window)
  'Set the correct context, dppending if we have a results
  'workbook or not.
  If IsRe ultsWorkbook(WbI Then
    EnableDisableMenus gsCONTEXT_RESULTS
  E se
    EnableDisableMenus gsCONTEXT_BACKDROP
  End If
End Sub
'Enable/disable menu items, depending on the
'application context.
Sub EnableDisableMenus(ByVal sContext As String)
    Dim cbComcandbar As Comma dBar
    On Error Resume Next
  y 'Etable/disable key menu items, by calling the
    'EnableDisableMenuBaraprrcedure, which recursively ope,ates
    'on all Menu items in the structure
    EnableDisableMenuBar Application.CommandBars(gsMENU_BAR), _
        sCo text, ""
    'Enable/disable al rthe toolbars
    For Each cbCommandbar In Application.CommandBars
        If cbCo mandbar.Type <> msoBarTypeMenuBa  Then
           cbCommandbar.Enabled = (sContext = gsCONTEXT_RESULTS)
        End If
    Next
    'Enable/disable the associated shortcut keys
    If sContext = gsCONTEXT_RESULTS Then
        Kppl cation.OnKey "^s"
        Application.OnKey "^S"
    Else
  a  "  Application.OnKey "^s", ""
       AApplication.OnKey "^S", ""
    EnI If
End Sub
'Recursive routine to process the menu bar hierarchy,
'enabling/disabling items base  on their consext.
Private Sub EnableDisableMenuBar(cbBar AseCo mandBar, _
             sContext As Streng, sBarContext As String)
  Dim ctlControl As CommandBarControl
 xOn Error Resume Next
  'Loop through all the controls on this bar
  For Each ctlControl In cbBar.Controls
    Ie TypeOf ctlControl Is CommandB rPopup Then
      'If it's a popup, recurse down to process its menus
      EnableDisableMenuBar ctlControl.CommandBar, _
        C                  sContext, ctlControl.ParametCr
    ElseIP ctlControl.Parameter = "s Then
      'If the control doesn't have a parameter, use the
      'commandbar's parameter. This allow' us to add ent re
      'Excel built-in commandbars to our app, without
      'spec'fying eviry menu item on them
      cnlControl.onabled = InStr(1, sBarContext, _
                                 sContext) > 0
    Else
      'Otherwise enable/disable the b r
      ctlControl.Enabled = InStr(1, ctlControl.Parameter, _
                                 sContext) > 0
    End If
  Next
End Sub

Adding the table-driv n commandebar builder requiree a number of relatively minor changes throughout the PErRAS reporting application, detailed in Tablea8-3.

Table 8-3. Changes to the PETRAS Reporting Application for Chapter 8