Understanding and using CommandBars, Part II (Creating your own)

Applies to: 2000, 2003, 2007, 2010, 2013 (desktop)

As stated in the first article,  Understanding CommandBars (Part 1), users of Windows applications expect to be able to right click their mouse button over a form or control and see a popup menu designed to provide options for the user based on where they are and what they are doing in the application.  The CommandBars built into Access do a pretty good job of meeting these needs, but custom applications require custom solutions and Access provides a robust way for you to create your own shortcut bars (at least for your desktop apps).

In that article, I briefly described how to identify the names of each of the Access CommandBars (also referred to as shortcut or popup menus) and the controls within each of those CommandBars.  In this article you will learn how to create your own shortcut menus, modify existing CommandBars, and implement custom shortcut menus within your applications.  The sample database contains all of the code used in the article.

If you are fortunate to have a copy of Access 2003 laying around, you can use the instructions in this link from the Microsoft Office web site to create your custom shortcut/popup  menus, and then import them into your application.  In later versions (2007+) of Access, this feature has been discarded from the user interface, so you are left with macros and VBA code to create your shortcut/popup menus.  This article describes how to create the VBA code to institute several popup menus in your applications.  It describes how to instantiate the menu, add controls to it, and use it in an application.

To start with, you should add the Microsoft Office XX Object (Image 1) library to your application references in the VB Editor menu.  When you do this, you will be able to take advantage of IntelliSense as you build your shortcut menus.  Once you are done creating your menus, you can remove this reference and make some minor modifications to your code (discussed later in the article) to implement late binding and provide compatibility of the shortcut menus across multiple versions of Office.

Step 1: Creating the code module and subroutine
The first step in defining a shortcut menu is to create a code module and subroutine to put it in.  You can build your menus and the supporting functions in your form class modules, and this can be beneficial if you are creating form specific functionality.  However if you are creating shortcut menus for use throughout your application, you will want to put these in a code module (Image 2) where the functions can be accessed no matter what form or report has the focus.   Additionally, by placing all of your shortcut menus in a single location, you will know exactly where to find them when (or if) you need to edit them.  The only thing you must consider if you place all of your shortcut menu definitions and Action functions in a standard code module is that if you want to reference a control on a form or report, you must explicitly define which form/report in your code.

Once you have created your code module, create the procedure which will actually build the menu.

Private Sub FormOptions
                      End Sub

Select all Open in new window

Step 2:  Define your CommandBar
In order to create your shortcut menu, you must declare your CommandBar object and the control objects that you will be using in the menu.

Dim cbr as CommandBar
                      Dim cbrButton As CommandBarButton 
                      Dim cbrcombo As CommandBarComboBox
                      Dim cbrEdit As CommandBarControl
                      'or
                      Dim cbr as Object         'Commandbar
                      Dim cbrButton As Object   'CommandBarButton 
                      Dim cbrcombo As Object    'CommandBarComboBox
                      Dim cbrEdit As Object     'CommandBarControl

Select all Open in new window

Note: The second method shown above is used when implementing late binding.

After declaring the CommandBar and the controls, you must instantiate the CommandBar.

set cbr = CommandBars.Add(Name, [Position], [Menubar], [Temporary])

Select all Open in new window

1. The first argument, [Name], of the CommandBars.Add method is the name you will use to refer to this CommandBar throughout your application.

2.  The second argument, [Position], defines the position of the CommandBar; for popup/shortcut menus this argument must be 5 (msoBarPopup).

3.  The third argument, [Menubar], is a true/false argument which indicates whether to replace the active menubar with this bar.  The default value is False, and I've never tried setting it to anything else.

4.  The last argument, [Temporary], determines whether the CommandBar will only be active in the current session of the database (True), or whether it will be a permanent part of the database (False).  If you set this argument to False and call the subroutine that creates the CommandBar, you should never have to call it again (in the active  database).
The sample database uses Temporary= True and re-instantiates the CommandBars each time the database is opened.

Step 3: Defining Menu Controls
CommandBar controls are similar to their form counterparts and include buttons, text boxes, and combo boxes.  The syntax to add a control to the command bar is simple:

Set ctrl = cbr.Controls.Add([Type], [ID], [Parameter], [Before], [Temporary])

Select all Open in new window

1.  The [Type] argument can contain any of the msoControl constants (msoControlButton, msoControlEdit, msoControlDropdown, msoControlComboBox, or msoControlPopup).  Use the numeric values or create your own constants if you intend to institute late binding.

2.  When you define your own shortcut menus, you are not limited to the controls you create, you can use the myriad of shortcut controls already built into Office (Cut, Paste, Print, Print Preview, Save, Delete, ...).  To take advantage of these Office shortcut controls in your menu, you simply have to set the [ID] argument in the controls.Add method to the [ID] value associated with the standard office control.  When you do this, your new control inherits all of the properties associated with the standard Office shortcut control (caption, image, and actions).

The challenge is determining the [ID] values of the various standard menu controls you want to use.  If you know the name of the CommandBar and caption of the control (or its position within the CommandBar) you can use the following line of code to get the ID value of the control.

Debug.print CommandBars(commandbarname).controls(controlcaption).ID
                      example: 
                      Debug.print CommandBars("Database Table/Query").controls("&Print...").ID

Select all Open in new window

If you don’t know the CommandBar name, or the precise caption of the control, use the following subroutine to print a list of the controls  (and their associated CommandBars)  which have a caption similar to what you are looking for.

Public Sub FindID(CaptionContains As String)
                          Dim cbr As CommandBar
                          Dim ctrl As CommandBarControl
                      
                          For Each cbr In Application.CommandBars
                              For Each ctrl In cbr.Controls
                      
                                  'Because many of the controls contain keyboard shortcuts, they 
                                  'have ampersands embedded within the caption that must be 
                                  'removed before doing the comparison to the search string.
                                  If InStr(Replace(ctrl.Caption, "&", ""), CaptionContains) > 0 Then
                                      Debug.Print cbr.Name, ctrl.Caption, ctrl.Id
                                  End If
                              Next
                          Next
                      End Sub

Select all Open in new window

Note: I found 50 CommandBar/control combinations that contain the caption “Print” (the ID for “&Print…” is 4, the ID for “Print Pre&view” is 109).

3.  The [Parameter] argument of the Add method allows you to assign a parameter value to a particular CommandBar control.  This is helpful if you want to run the same function for multiple CommandBar controls but pass the function a different value for each CommandBar button (this technique is used in the FormOptions and FormReports code in the sample database).

4.  The [Before] argument indicates the position of the new control within the CommandBar;  the control will be inserted in the CommandBar controls collection immediately before the control that is currently in that position (and will assume that position).  If you use a value larger than the number of controls in the collection plus one (> .count+1), Access will raise RunTime error #9, (Subscript out of range).  To avoid this error, use the default and simply add the controls to the CommandBar in the order in which you want them to appear.
Note:If you are trying to modify existing Office CommandBars, you will only be able to append the new controls to the end of the menu list.

5.  The final argument, [Temporary] has the same purpose for controls as for the CommandBar object; it determines whether the control is a temporary or permanent addition to the CommandBar.  If you create the CommandBar as Temporary, this setting is irrelevant as the CommandBar will be dropped from the database when you close it.  However, if you create the CommandBar as permanent (Temporary:=False), but set the controls Temporary property to true, then the next time the database is opened, the CommandBar will be present, but the control will not.  This is especially useful if you want to temporarily add controls to an already existing Office shortcut menu.  The code in the sample database uses this method to temporarily add a button to the Form Datasheet Row CommandBar, allowing the user to right-click on a row in the datasheet and generate a details report that is specific to the selected record.

Control Properties
Like the controls placed on forms and reports, CommandBar controls have numerous properties associated with them.  Buttons are like toggle buttons and include a “state” property which indicates whether the button is Up or Down.  Text boxes and combo boxes operate similar to their form and report counterparts.  

1.  All of these controls share a base set of control properties, the most critical of which is the “Caption” property.  The "Caption" performs dual roles as both the control label and the control name.

Ctrl.Caption = “&Active Only”

Select all Open in new window

If the control is a button, the caption is displayed on the button.  If the control is a combo box or textbox, the caption will display to the left of the actual control, as though it were the label associated with the control.  The other purpose for the caption property is to use it to refer to the control object, as in:

set ctrl = cbr.controls(“&Active Only”)

Select all Open in new window

2.  The purpose of shortcut menus is to simplify tasks, but it is left up to you (the developer) to define what you want to accomplish when a shortcut menu button is clicked, a combo box item is selected, or text is entered in a textbox.  You do this by defining the "OnAction" property of each of the controls.  If you fail to define the OnAction property of a button, clicking it will will have no effect.  If you fail to define the OnAction property of a combo box or textbox, then selecting an item from the combo box or entering text into a text box will update the “value” of the control, but will not perform any other action.  The syntax for the OnAction property is:

Ctrl.OnAction = “=fnSomeFunctionName()”

Select all Open in new window

The function defined by the text within the quotes in the example above must be the name of a PUBLIC function, must be preceded by the equal sign, and must contain the opening and closing parenthesis at the end of the string.  If the function is defined as having mandatory parameters, then you must pass a value to the function within the parenthesis, just like you would if you were calling that function from some other code module in your form.

Ctrl.OnAction = “=fnSomeFunctionName(1)”
                      'Or
                      Ctrl.OnAction = “=fnSomeFunctionName(‘SomeTextValue’)”

Select all Open in new window

An alternative to this method is to define your functions without the arguments and use the [Parameter] property of the control to assign a value to the control.  If you use this technique, then within the function defined by your OnAction property, you can determine the value of the parameter of the control that was clicked with the following line of code.

CommandBars.actioncontrol.parameter

Select all Open in new window

There are a number of other properties associated with each control which you can browse using IntelliSense.  Generally, the Tag (used like a controls Tag property) and BeginGroup (draws a line in the menu above the current control) are the most commonly used of the other control properties.

Working with buttons
As mentioned above, buttons function similar to toggle buttons, but unless you programmatically change the state of the button it will remain in that state when you click it and execute the code defined in the OnAction property of the button.  In the example database, the Options button pops up a menu that contains two buttons (Display All and Display Active).  You don't really need both of these buttons since Display Active, with no check-mark next to it would probably represent Display all, but I've included both to show how to assign a parameter to the control and then use that parameter in the associated OnAction function call.  The code to create shortcut menu, with just the two buttons would look like:

Public Sub FormOptions(Optional Reset As Boolean)
                      
                          'the constants used in this code are declared in the example database
                      
                          Dim cbr As CommandBar 'Object   '
                          Dim cbrButton As CommandBarButton 'Object  '
                      
                          On Error GoTo FormOptionsError
                          
                          If CmdBarExists("FormOptions") Then   'see sample database
                              If Reset = False Then Exit Sub
                              DeleteCmdBar "FormOptions"        'see sample database
                          End If
                          
                          'Instantiate the commandbar
                          Set cbr = CommandBars.Add("FormOptions", BarPopup, , True)
                      
                          'Create the two buttons
                          With cbr
                      
                              Set cbrButton = cbr.Controls.Add(ControlButton, , , , True)
                              With cbrButton
                                  .Caption = "Display All"
                                  .Parameter = "All"
                                  .State = -1
                                  .OnAction = "=fnFormOptionsDisplayRecords()"
                              End With
                      
                              Set cbrButton = cbr.Controls.Add(ControlButton, , , , True)
                              With cbrButton
                                  .Caption = "Display Active"
                                  .Parameter = "Active"
                                  .OnAction = "=fnFormOptionsDisplayRecords()"
                              End With
                      
                          End With
                      
                          Exit Sub
                      
                      FormOptionsError:
                          
                          MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly, "FormOptions Menu error"
                          Debug.Print "FormOptions error", Err.Number, Err.Description
                          
                      End Sub

Select all Open in new window

This code includes calls to several other procedures (CmdBarExists, DeleteCmdBar) which are provided in the example database.  You should note that the State of the "Display All" button is initially set to -1, to place a check-mark next to it.  Also note that both buttons call the same function in their OnAction events, and have different parameter values.  Use of that parameter value can be seen in the following OnAction function:

Public Function fnFormOptionsDisplayRecords()
                      
                          Dim strAction As String
                          
                          strAction = CommandBars.ActionControl.Parameter
                          
                          'Change the state of the two buttons
                          CommandBars("FormOptions").Controls("Display All").State = (strAction = "All")
                          CommandBars("FormOptions").Controls("Display Active").State = (strAction = "Active")
                          
                          'Filter or clear the filter, depending on which button was selected
                          If strAction = "All" Then
                              Form_frm_CommandBars.sub_Presidents.Form.Filter = ""
                              Form_frm_CommandBars.sub_Presidents.Form.FilterOn = False
                          Else
                              Form_frm_CommandBars.sub_Presidents.Form.Filter = "[Active] = -1"
                              Form_frm_CommandBars.sub_Presidents.Form.FilterOn = True
                          End If
                      
                      End Function

Select all Open in new window

This code determines which of the two buttons was clicked and assigns that string value to the variable strAction.  It then toggles the two buttons on/off (up/down) based on the value of strAction.  And finally, the code in the If / End If segment either sets the subforms filter or clears it.

Working with ComboBoxes
Shortcut menu combo boxes only contain a single column, do not have a RecordSource property, and do not have  a "Value" property.  Despite these drawbacks, they can be very useful tools in your shortcut menus.  The FormOptions CommandBar in the sample database contains a combo box which performs the same function as the two buttons (Display All and Display Active) discussed above.  The code that creates this combo in the CommandBar is:

Set cbrcombo = cbr.Controls.Add(ControlComboBox, , , , True)
                      With cbrcombo
                          .Caption = "Display records:"
                          .BeginGroup = True
                          .Width = 150
                          .DropDownWidth = 100
                          .AddItem "All"
                          .AddItem "Active"
                          .OnAction = "=fnFormOptionsDropdown()"
                          .ListIndex = 1
                      End With

Select all Open in new window

The differences between this and the button code are the addition of the DropDownWidth property to define the width of the dropdown, the addition of the .AddItem methods to add items to the combo boxes list, and the addition of the ListIndex property to set the default "value" of the combo box.  When determining the "Value" of the combobox in function fnFormOptionsDropDown(), you must use the controls ListIndex property.  Although this is not as simplistic as the using the "Parameter" property of the button control, it provides the ability to add add multiple values in a small space.  If you have a large number of items to add to the combo box, you can create a recordset and loop through that recordset to add multiple values to the combo.  If you really wanted to, you could create a cascading combo box functionality by using the controls .Clear method and then rebuilding a comboboxes Item list in the function called by the OnAction event of another combo box.

Working with textboxes
Personally, I've found very little use for textboxes in my shortcut menus;  one purpose might be to enter values to be used in a search function or filter.  The sample database includes code to create a textbox in the FormOptions menu:

Set cbrEdit = cbr.Controls.Add(ControlEdit, , , , True)
                      With cbrEdit
                          .Caption = "Year:"
                          .OnAction = "=fnFormOptionsYear()"
                      End With

Select all Open in new window

and to display the entered value in a message box after it is entered.  Note that the CommandBar textbox does not have a "Value" property.  You must refer to the "Text" property of the control to determine what was entered into that control:

    MsgBox CommandBars("FormOPtions").Controls("Year:").Text

Select all Open in new window

Working with Popups
Yes, you can embed shortcut menus inside of other shortcut menus.  To do this, use the
msoControlPopup constant (or its associated value: 5) when you define your control.  Then treat it as though it were a separate CommandBar and assign controls to it within a With / End With code segment.  The sample database does not include an example of how to do this.

Implementing your custom CommandBars in your application
If you choose to instantiate your custom CommandBars as Permanent, you will only need to run the code that creates them once for each database.  If you have a standard set which you will want to use repeatedly you might run that code in your New Database template; in which case you would never need to call them, unless you decided to modify them.  However, if you instantiate your custom CommandBars as Temporary, you will need to call the code that creates them every time the database opens; I do it in the Timer event of my splash form or in the Open event of some forms if they have a CommandBar which is unique to a form which may never get opened during a particular session.

The easiest way to implement custom shortcut menus in your application is to set the "Shortcut Menu Bar" property of your forms, reports, and controls to the Name you assigned to your CommandBar.  When implemented in this fashion, your custom CommandBar will replace the standard Office shortcut menu in that context.  I've found that this does not work with command buttons (at least not in 2007 and 2010).Furthermore, unless you assign a custom CommandBar to the form as well, you will still see the standard form shortcut menu when you click on the forms title bar.

To disable the forms standard shortcut menu, you must turn set the forms Shortcut Menu property to No, but this disables the shortcut menus for all of the controls on that form as well.  There is, however, a way that to disable a forms Shortcut Menus (set the property to No) and still enable all of your custom shortcut menus.  Instead of assigning your menus to the Shortcut Menu Bar property of your forms, reports, and controls, you can use the MouseUp event associated with those objects.  This is implemented in the FormHeader_MouseUp event with the following line of code.

If Button = acRightButton Then CommandBars("FormMenu").ShowPopup

Select all Open in new window

This process a little more effort, since you must add a line of code to the MouseUp event of each of the controls where you want to implement your shortcuts, but it is worth it to get rid of the form shortcut menu and still have access to your custom Commandbars.

Late Binding
The advantage of late binding is that you let Access determine which library references are necessary for your application, and it will select the appropriate version based on the version of Office that is being run.  If you choose to use Early Binding, you should ensure that you compile your code on a machine running the earliest version of Office your users are likely to encounter.  Office does a good job of updating references to later versions, but fails every time if you compile the code on a machine with a later version of Office than one of your users has on their desk.  To implement late binding, you simply change the object declarations from "as CommandBar" or "as CommandBarButton" to "as Object".  You will also need to replace all references to the constants associated with Microsoft Office XX Object library, and then remove that reference from your project.  Your code will not compile properly if you have forgotten to change one of the object declarations or have used an invalid constant.

Manipulating standard Office Shortcut menus
In addition to creating your own Commandbars, you can manipulate the standard Office shortcut menus as well.  In the sample database, you will find that this is accomplished in the Form_Open and Form_Close event of datasheet subform.  The datasheet normally has three CommandBars associate with it, one for Rows, one for Columns, and the other for Cells.  

1.  In the Form_Open event, the FormDatasheetMenu subroutine adds a button to the "Form Datasheet Row" CommandBar;  the last item in that list.

2.  The FormDatasheetRowMenu subroutine hides/reveals all of the other items in the "Form Datasheet Row" CommandBar.  You can see the effect of this by changing the value of the Datasheet Row Menu option group and then right clicking on the datasheets record select button.  

3.  The final line of code in that subroutine enables/disables the "Form Datasheet Cell" CommandBar, the one that would normally popup if you right click in a datasheet cell.  CommandBars do not have a Visible property, to hide them, you simply set the Enabled property to False.  You can see this in action by changing the option in the "Datasheet Cell Menu" option group, and then right clicking in one of the datasheet cells.

Conclusion:
Shortcut menus are a critical aspect of Windows applications and VBA is a powerful tool for creating your own or manipulating the standard Office shortcut menus.

If you found this article helpful, please don't forget to vote "Yes"!
CommandBars-II.mdb