لطفا منتظر باشید ...
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 Figure 8-24 to refresh your memory.
Figure 8-24. The PETRAS Add-in Toolbar
[View full size image]
PETRAS Timesheet
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.
Figure 8-25. The PETRAS Add-in Command Bar Definition Table
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: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:
Application.CommandBars(gsBAR_TOOLBAR).Delete
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
ResetCommandBars
PETRAS Reporting
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.
Figure 8-26. The PETRAS Reporting Command Bar Definition Table
[View full size image]
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!Application Contexts
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 enabledResults
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.
Listing 8-10. The Code to Implement Application Contexts
Adding the table-driven command bar builder required a number of relatively minor changes throughout the PETRAS reporting application, detailed in Chapter 8
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
