Creating Custom Command Bars and Their Controls in Code

3 4

The CommandBars collection that Access developers use to create custom toolbars and menu bars in VBA is part of the Office object model. Office command bars are used in all the major Office applications. To create a new Access command bar in VBA code, you use the Add method of the CommandBars collection of the Access Application object. To add controls to the new command bar, use the Add method of the Controls collection of the new toolbar. Both command bars themselves and the controls on them have a great number of properties, but generally you need to set only a few properties to create a useful toolbar or menu bar; the others are fine left at their defaults.

Assigning Images to Toolbar Buttons Using FaceID

When you create toolbar buttons in VBA code, you assign an image to the toolbar by using the FaceId property, which takes a numeric value. You can use any image that has been assigned to any toolbar button in the application (you have several thousand to choose from), but the catch is that you have to know the image’s FaceId number. Microsoft Office XP Developer 2000 included a function that creates a custom toolbar with all the available images and their numbers. The sample database that included this useful function didn’t make it into Office XP Developer, but you can find it on the companion CD, in the modCommandBarCode module of the Test Access 2002 database.

Note that some of the Access databases for this book rely on objects in Microsoft Word 2002. In some cases, the databases use Word to create documents filled with Access data. In other cases, they use the Word System object to get the Documents or Templates path without using the Registry. Because of the references to Word objects, you will need to have Word 2002 installed to use these databases without error.

The function is listed here.

 Function CBShowButtonFaceIDs(lngIDStart As Long, _                              lngIDStop As Long)     ' This procedure creates a toolbar with buttons that     ' display the images associated with the values starting     ' at lngIDStart and ending at lngIDStop.   Dim cbrNewToolbar  As CommandBar   Dim cmdNewButton  As CommandBarButton   Dim intCntr     As Integer        ' If the ShowFaceIds toolbar exists, delete it.     On Error Resume Next     Application.CommandBars("ShowFaceIds").Delete     ' Create a new toolbar.     Set cbrNewToolbar = Application.CommandBars.Add _         (Name:="ShowFaceIds", temporary:=True)     ' Create a new button with an image matching the FaceId     ' property value indicated by intCntr. For intCntr = lngIDStart To lngIDStop         Set cmdNewButton = _             cbrNewToolbar.Controls.Add(Type:=msoControlButton)         With cmdNewButton              ' Setting the FaceId property value specifies the              ' appearance but not the functionality of the button.              .FaceId = intCntr              .TooltipText = "FaceId = " & intCntr         End With     Next intCntr     ' Show the images on the toolbar.     With cbrNewToolbar          .Width = 600          .Left = 100          .Top = 200          .Visible = True     End With End Function 

When run with the arguments 1, 500, this function produces the toolbar shown in Figure 13-38. (Be careful not to dock this toolbar. Only a few of its images are visible when the toolbar is docked.)

figure 13-38. this toolbar displays the specified range of the available button images.

Figure 13-38. This toolbar displays the specified range of the available button images.

To determine an image’s FaceId number, hover the mouse pointer over the image on the toolbar; the number will appear in a ScreenTip.

If you aren’t familiar with writing VBA code, you might want to read Chapter 20, "Customizing Your Database Using VBA Code," and then return to this section.

The Add method of the CommandBars collection of the Application object has the following syntax:

 Application.CommandBars.Add(Name, Position, MenuBar, Temporary) 

Table 13-2 lists the Add method’s parameters.

Table 13-2. The parameters of the CommandBars collection’s Add method

Parameter Description

Name

(Optional) The name of the new command bar. If this parameter is omitted, the command bar will have a default name of Custom 1 (or some other number).

Position

(Optional) The command bar’s position. Set this parameter with one of the msoBarPosition constants listed in Table 13-3.

MenuBar

If True, the Access menu bar is replaced by the new command bar. (False is the default value.)

Temporary

Set to True to make the new command bar temporary (meaning that it will be deleted when Access is closed), Set to False (the default setting) to make the new command bar permanent.

Table 13-3 lists the Position parameter’s constants.

Table 13-3. The named constants used to set the Position parameter of the Add method

Named constant Description

msoBarBottom

The new command bar is positioned at the bottom of the Access window.

msoBarFloating

The new command bar is a floating toolbar.

msoBarLeft

The new command bar is positioned at the left of the Access window.

msoBarMenuBar

The new command bar is a menu bar.

msoBarPopup

The new command bar is a shortcut menu.

msoBarRight

The new command bar is positioned at the right of the Access window.

msoBarTop

The new command bar is positioned at the top of the Access window.

Adding Controls to Command Bars

Because a toolbar without controls isn’t very useful, the next step is to add one or more controls to the new toolbar or menu bar. You can use VBA code to add three types of controls to a command bar: CommandBarButton, CommandBarComboBox, and CommandBarPopup. (Only a CommandBarButton control can be added to a command bar in the interface.)

A CommandBarButton control executes a single command. Typically, it’s represented as a button on a toolbar or a text command on a menu. The CommandBarComboBox control is a drop-down list of selections, with or without a box in which text can be entered. Built-in Access toolbars contain several such controls, such as the Object, Font, and Size drop-down lists on the Form/Report Formatting toolbar, shown in Figure 13-39.

figure 13-39. a built-in access toolbar has a commandbarcombobox control.

Figure 13-39. A built-in Access toolbar has a CommandBarComboBox control.

The third type of control, the CommandBarPopup control, isn’t found on any built-in Access toolbar, but the View selector on the Microsoft Outlook 2000 toolbar is a CommandBarPopup control. This control is a shortcut menu. It combines the features of a command button and a drop-down list but is easier to use because you don’t have to click on a tiny drop-down button, as with a combo box control. To use a pop-up control, click the button on the toolbar, move the mouse pointer down to the command you want to run, and release the mouse button. If you move your mouse pointer away from the list without making a selection, the list closes on its own. This behavior is pleasantly different from the behavior of a drop-down list in an Access combo box, which requires that you either make a selection from the list or press Esc to collapse the list.

Each type of command bar control has several options controlling its appearance and functionality. Some of these options can be set only from VBA code. For the CommandBarComboBox and CommandBarPopup controls, you need to set the list items (or menu selections) in code, as opposed to the simpler CommandBarButton control, which executes a single command.

The syntax for the Add method of the Controls collection of a command bar is shown here:

 cbr.Controls.Add(Type, Id, Parameter, Before. Temporary) 

Table 13-4 describes the Add method’s parameters.

Table 13-4. The parameters of the Controls collection’s Add method

Parameter Description

Cbr

A variable representing a specific CommandBar control.

Type

The type of control to add to the command bar; one of the constants in Table 13-5.

Id

(Optional) An integer that specifies a built-in control. If set to 1 or omitted, a blank custom control of the specified type will be added to the command bar.

Parameter

(Optional) For custom controls, can be used to send information to VBA procedures, similar to the Tag property.

Before

(Optional) A number that indicates the control’s position on the command bar. If omitted, the control is added at the end.

Temporary

(Optional) True if the control is temporary and will be deleted when Access is closed; False (the default value) if the control is permanent.

Table 13-5 describes the command bar control type constants.

Table 13-5. Command bar control type constants

Constant Description

msoControlButton

Button

msoControlComboBox

Combo box

msoControlDropdown

Drop-down list

msoControlEdit

Text box

msoControlPopup

Pop-up menu

CommandBarComboBox Variations

Although the Type parameter has five selections, only three types of command bar buttons are available in Access: msoControlComboBox, msoControlDropdown, and msoControlEdit. All three selections create CommandBarComboBox controls, with variations in appearance and functionality, as follows:

  • A CommandBarComboBox created by using the msoControlComboBox Type setting is a control with a drop-down list and a box in which text can be entered.
  • A CommandBarComboBox created by using the msoControlDropdown Type setting is a control with a drop-down list only.
  • A CommandBarComboBox created by using the msoControlEdit Type setting is a text box only.

The following code sample creates a custom toolbar with five controls, one for each available type. You add items to the lists of the combo box and drop-down controls by using the AddItem method. For the pop-up control, which is itself a type of menu, you must add separate button controls representing the commands on the menu’s list.

You’ll find this and the following procedures in this chapter in the basCommandBars.bas module of the Test Access 2002 database on the companion CD.

 Sub CreateTestBar()     Dim cbrTest As Office.CommandBar     Dim btnButton As Office.CommandBarButton     Dim btnComboBox As Office.CommandBarComboBox     Dim btnDropDown As Office.CommandBarComboBox     Dim btnTextBox As Office.CommandBarComboBox     Dim btnPopUp As Office.CommandBarPopup     Dim btn1 As Office.CommandBarButton     Dim btn2 As Office.CommandBarButton     Dim btn3 As Office.CommandBarButton     Dim btn4 As Office.CommandBarButton         Set cbrTest = Application.CommandBars.Add(Name:= _         "Test Toolbar", Position:=msoBarFloating, _         temporary:=False)     cbrTest.Enabled = True     cbrTest.Visible = True         'Creates a button that beeps when clicked     Set btnButton = cbrTest.Controls.Add(Type:=msoControlButton)         With btnButton         .Style = msoButtonIcon         .FaceId = 68         .Caption = "Beep"         .Style = msoButtonIconAndCaption         .Tag = "Test Button"         .OnAction = "mcrBeep"     End With         'Creates a combo box in which a beverage can be selected     ' and saved to a table     Set btnComboBox = cbrTest.Controls.Add(msoControlComboBox)         With btnComboBox         .AddItem "Coffee", 1         .AddItem "Tea", 2         .AddItem "Water", 3         .AddItem "Milk", 4         .AddItem "Orange Juice", 5         .Caption = "Select Beverage"         .Tag = "Test Combo Box"         .OnAction = "SaveBeverage"         .Style = msoComboLabel         .DropDownLines = 8         .DropDownWidth = 75     End With     'Creates a drop-down list in which an animal can be selected     ' and saved to a table     Set btnDropDown = cbrTest.Controls.Add( _         Type:=msoControlDropdown)         With btnDropDown         .AddItem "Dog", 1         .AddItem "Cat", 2         .AddItem "Parrot", 3         .AddItem "Kangaroo", 4         .AddItem "Wildebeest", 5         .DropDownLines = 8         .DropDownWidth = 75         .Style = msoComboLabel         .Caption = "Select Animal"         .Tag = "Test Drop-down"         .OnAction = "SaveAnimal"     End With         'Creates a text box in which a promo can be entered; it is     ' picked up when a meeting date is selected in the pop-up     ' control.     Set btnTextBox = cbrTest.Controls.Add(Type:=msoControlEdit)         With btnTextBox         .Caption = "Special Promo"         .Style = msoComboLabel         .Text = "10% Off"     End With         'Creates a pop-up menu for selecting a meeting date; the     ' meeting date and the promo entered in the text box control     ' are saved in a table.     Set btnPopUp = cbrTest.Controls.Add(Type:=msoControlPopup)         With btnPopUp         .Caption = "Move Meeting Date"         .Tag = "Test PopUp"                Set btn1 = .Controls.Add(Type:=msoControlButton)         With btn1             .Caption = "Today"             .OnAction = "MoveToToday"             .Style = msoButtonCaption         End With                Set btn2 = .Controls.Add(Type:=msoControlButton)         With btn2             .Caption = "Tomorrow"             .OnAction = "MoveToTomorrow"             .Style = msoButtonCaption         End With                Set btn3 = .Controls.Add(Type:=msoControlButton)         With btn3             .Caption = "Day after Tomorrow"             .OnAction = "MoveToDayAfterTomorrow"             .Style = msoButtonCaption          End With                 Set btn4 = .Controls.Add(Type:=msoControlButton)          With btn4             .Caption = "Next Monday"             .OnAction = "MoveToNextMonday"             .Style = msoButtonCaption         End With            End With         cbrTest.Visible = True End Sub 

Figure 13-40 shows the toolbar created by this code.

figure 13-40. this toolbar, created from vba code, contains several different types of controls.

Figure 13-40. This toolbar, created from VBA code, contains several different types of controls.

When a selection is made from the pop-up control, one of several functions runs, writing data to a table (tblInfo) that can be used as a source of lookup information in the database. These functions are listed here:

 Function MoveToToday()     Dim strPromo As String     Dim dbs As DAO.Database     Dim rst As DAO.Recordset     Dim cbr As Office.CommandBar     Dim cbo As Office.CommandBarComboBox         Set cbr = Application.CommandBars("Test Toolbar")     Set cbo = cbr.Controls("Special Promo")     Set dbs = CurrentDb     Set rst = dbs.OpenRecordset("tblInfo")     With rst         .MoveFirst         .Edit         ![Promo] = Nz(cbo.Text, "10% Off")         ![MeetingDate] = Date         .Update         .Close     End With     End Function Function MoveToTomorrow()     Dim strPromo As String     Dim dbs As DAO.Database     Dim rst As DAO.Recordset     Dim cbr As Office.CommandBar     Dim cbo As Office.CommandBarComboBox         Set cbr = Application.CommandBars("Test Toolbar")     Set cbo = cbr.Controls("Special Promo")     Set dbs = CurrentDb     Set rst = dbs.OpenRecordset("tblInfo")     With rst         .MoveFirst         .Edit         ![Promo] = Nz(cbo.Text, "10% Off")         ![MeetingDate] = Date + 1         .Update         .Close     End With End Function Function MoveToDayAfterTomorrow()     Dim strPromo As String     Dim dbs As DAO.Database     Dim rst As DAO.Recordset     Dim cbr As Office.CommandBar     Dim cbo As Office.CommandBarComboBox         Set cbr = Application.CommandBars("Test Toolbar")     Set cbo = cbr.Controls("Special Promo")     Set dbs = CurrentDb     Set rst = dbs.OpenRecordset("tblInfo")     With rst         .MoveFirst         .Edit         ![Promo] = Nz(cbo.Text, "10% Off")         ![MeetingDate] = Date + 2         .Update         .Close     End With End Function Function MoveToNextMonday()     Dim strPromo As String     Dim dbs As DAO.Database     Dim rst As DAO.Recordset     Dim dteTest As Date     Dim cbr As Office.CommandBar     Dim cbo As Office.CommandBarComboBox         Set cbr = Application.CommandBars("Test Toolbar")     Set cbo = cbr.Controls("Special Promo")         dteTest = Date + 1     Do While Weekday(dteTest) <> vbMonday         dteTest = dteTest + 1         Debug.Print "Testing " & dteTest     Loop                Set dbs = CurrentDb     Set rst = dbs.OpenRecordset("tblInfo")     With rst         .MoveFirst         .Edit         ![Promo] = Nz(cbo.Text, "10% Off")         ![MeetingDate] = dteTest         .Update         .Close     End With     End Function 

InsideOut

For CommandButtonComboBox controls, you have an alternative method of responding to the user’s selection (or information entry): You can use the control’s Change event to execute the procedure specified in the control’s On Action property. The online help says that to use this technique you must set up a variable for the CommandButtonComboBox control by using the WithEvents keyword in a class module. However, I’ve found that this is not so (at least in Access). The OnAction function fires when a new item is selected in the list, without the need to set up an event handler for the control.

The following functions are run from the On Action properties of the combo box control and drop-down list control on the test toolbar:

 Public Function SaveBeverage()     Dim strPromo As String     Dim dbs As DAO.Database     Dim rst As DAO.Recordset     Dim cbr As Office.CommandBar     Dim cbo As Office.CommandBarComboBox         Set cbr = Application.CommandBars("Test Toolbar")     Set cbo = cbr.Controls("Select Beverage")     Set dbs = CurrentDb     Set rst = dbs.OpenRecordset("tblInfo")     With rst         .MoveFirst         .Edit         ![Beverage] = Nz(cbo.Text)         ![MeetingDate] = Date         .Update         .Close     End With     End Function 

Troubleshooting - My combo box drop-down list doesn’t appear in the right place

When you select a toolbar combo box control’s drop-down list, the list appears under the caption, not under the box at the top of the list, as shown in Figure 13-41. This is the opposite of the way combo boxes work on Access forms, and if you use combo boxes on both forms and toolbars, this discrepancy will be disconcerting to users. Avoid this problem by using pop-up controls (instead of combo box controls) on toolbars.

figure 13-41. this commandbarcombobox control is shown with its list selected.

Figure 13-41. This CommandBarComboBox control is shown with its list selected.

 Public Function SaveAnimal()     Dim strPromo As String     Dim dbs As DAO.Database     Dim rst As DAO.Recordset     Dim cbr As Office.CommandBar     Dim cbo As Office.CommandBarComboBox         Set cbr = Application.CommandBars("Test Toolbar")     Set cbo = cbr.Controls("Select Animal")     Set dbs = CurrentDb     Set rst = dbs.OpenRecordset("tblInfo")     With rst         .MoveFirst         .Edit         ![Animal] = cbo.Text         ![MeetingDate] = Date         .Update         .Close     End With     End Function 

Figure 13-42 shows tblInfo, with information written to its fields from the test toolbar’s controls.

figure 13-42. this access table is filled with information from selections made in custom toolbar controls.

Figure 13-42. This Access table is filled with information from selections made in custom toolbar controls.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net