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 Figure 8-24 to refresh your memory.
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 and scrolled the right pane to column O to show some of the more interesting control settings. To see the entire table, just set the IsAddin property of the PetrasAddin.xla workbook to False so the wksCommandBars worksheet is visible in the Excel user interface.
The code within the add-in looks exactly like it did when we last saw it except for the addition of two code modules: MCommandBars and MPastePicture. These hold the code that reads the command bar definition table and builds the command bars it specifies. The procedure call used to create the command bar in the Auto_Open procedure is exactly the same as before. The difference is now it calls the BuildCommandBars procedure in the MCommandBars module instead of our previous, hard-coded command bar building procedure of the same name that was located in the MSystemCode module.
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 PETRAS timesheet application to implement the table-driven command bar builder is shown in Chapter 8
Module
Procedure
Change
MCommandBars (new module)
New module containing the command bar building code.
MPastePicture (new module)
New module to support the command bar builder. Used to add a picture to and retrieve a picture from the clipboard.
MOpenClose
Auto_Open
The BuildCommandbars procedure is now called from the new MCommandBars module instead of MSystemCode.
MSystemCode
BuildCommandBars
This procedure was removed because the task of building the command bars is now handled by the table-driven command bar builder.
Previous versions of the PETRAS reporting application have had a very simple menu structure, little more than the usual File > New, Open, Close, Save and Exit menus and a Window menu to switch between results workbooks. When we''re displaying a results workbook, however, we would really like to provide most (but not all) of Excel''s built-in menus, to enable our users to work directly with the workbook. Adding the command bar builder to the application makes this a trivial task of including the appropriate built-in menu IDs. In the definition table shown in Figure 8-26, 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.
If you look at the OnAction and Control 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!
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:
Backdrop The static backdrop sheet is being displayed, so almost all menus not related to beginning work or exiting the application are disabled.
DataEntry We''re in a data-entry worksheet, 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 is usually determined by the worksheet currently being displayed, we can use the application WindowActivate event to trigger the enabling/disabling by using code like that shown in Listing 8-10.
Private Sub mxlApp_WindowActivate(ByVal Wb As Workbook, _ ByVal Wn As Window) ''Set the correct context, depending if we have a results ''workbook or not. If IsResultsWorkbook(Wb) Then EnableDisableMenus gsCONTEXT_RESULTS Else EnableDisableMenus gsCONTEXT_BACKDROP End If End Sub ''Enable/disable menu items, depending on the ''application context. Sub EnableDisableMenus(ByVal sContext As String) Dim cbCommandbar As CommandBar On Error Resume Next ''Enable/disable key menu items, by calling the ''EnableDisableMenuBar procedure, which recursively operates ''on all Menu items in the structure EnableDisableMenuBar Application.CommandBars(gsMENU_BAR), _ sContext, " ''Enable/disable all the toolbars For Each cbCommandbar In Application.CommandBars If cbCommandbar.Type <> msoBarTypeMenuBar Then cbCommandbar.Enabled = (sContext = gsCONTEXT_RESULTS) End If Next ''Enable/disable the associated shortcut keys If sContext = gsCONTEXT_RESULTS Then Application.OnKey "^s" Application.OnKey "^S" Else Application.OnKey "^s", " Application.OnKey "^S", " End If End Sub ''Recursive routine to process the menu bar hierarchy, ''enabling/disabling items based on their context. Private Sub EnableDisableMenuBar(cbBar As CommandBar, _ sContext As String, sBarContext As String) Dim ctlControl As CommandBarControl On Error Resume Next ''Loop through all the controls on this bar For Each ctlControl In cbBar.Controls If TypeOf ctlControl Is CommandBarPopup Then ''If it''s a popup, recurse down to process its menus EnableDisableMenuBar ctlControl.CommandBar, _ sContext, ctlControl.Parameter ElseIf ctlControl.Parameter = " Then ''If the control doesn''t have a parameter, use the ''commandbar''s parameter. This allows us to add entire ''Excel built-in commandbars to our app, without ''specifying every menu item on them ctlControl.Enabled = InStr(1, sBarContext, _ sContext) > 0 Else ''Otherwise enable/disable the bar ctlControl.Enabled = InStr(1, ctlControl.Parameter, _ sContext) > 0 End If Next End Sub
Adding the table-driven command bar builder required a number of relatively minor changes throughout the PETRAS reporting application, detailed in Chapter 8
Module
Procedure
Change
MOpenClose
Auto_Open
Set initial application context at end of routine.
MCommandBars
Replaced the entire module with the table-driven command bar builder.
MPastePicture (new module)
New module to support the command bar builder. Used to add a picture to and retrieve a picture from the clipboard.
MGlobals
Added constants for application contexts.
MEntryPoints
MenuWindowSelect
We were using the Parameter to test for the PETRAS Backdrop menu item. Changed to use the caption instead, as the Parameter is now used for the application context.
CAppEventHandler
mxlApp_WindowActivate
Identify the application context and pass it to EnableDisableMenus, as shown in Listing 8-10.
MSystemCode
EnableDisableMenus
Implemented Listing 8-10, to enable/disable the menus based on application context instead of hard-coding each menu item.
MSystemCode
AddToWindowMenu
Set the Parameter value to Backdrop,Results when adding the workbook window menu items.
MWorkspace
RestoreExcelSettings
Moved the code to re-enable the toolbars to here, from the old RestoreMenus routine (which has been replaced by the command bar builder).
wksCommandBars (new worksheet)
New worksheet to hold the command bar definition table.
![[View full size image]](/image/library/english/13850_08fig24_alt.gif;380140){kind=link}
![[View full size image]](/image/library/english/13850_08fig26_alt.jpg;380140){kind=link}