The Right-Click shortcut (PopUp) menus are an integral part of the Windows environment and users expect to see them in their custom applications. However, there are times when you don't want to present your users with the standard set of shortcut options available in the current context of your application. In those instances, you can either manipulate those that already exist, or you can create your own.
In Access versions prior to 2007 there was a convenient method for creating shortcut menus, but that method was eliminated in Access 2007. If you are fortunate enough to still have a copy of Access 2003 (or an earlier version), you can still create custom toolbars using that method and then import them into your 2007+ application using the External Data import method, by checking the "Menus and Toolbars" Import option.
I will expand on the concept of creating your own toolbars later, for now lets focus on understanding the existing CommandBars, specifically the shortcut menus (or popup toolbars).
When I type:
?application.commandbars.Count
in my immediate window, I get 199. Some of these are not shortcut menus, so the actual number in Access 2007 is somewhere around 130. You can generate a list of those or dump those into table with a subroutine similar to:
Public Sub CbrShortCutMenus(Optional SearchFor As String)
Dim cbr As Object
Dim intCount As Integer
For Each cbr In Application.CommandBars
If cbr.Type = 2 Then
If SearchFor = "" Then SearchFor = " "
If InStr(cbr.Name & " ", SearchFor) > 0 Then Debug.Print cbr.Name
End If
Next
End Sub
This routine generates a list of all the shortcut menus. If you include the optional [SearchFor] argument it will only list those where the search string is included somewhere in the commandbars name (there are 20 that contain the word 'Form' somewhere in the name, 17 contain the word 'Datasheet'). However, that subroutine only gives you the names of the menus, and the name is not always self explanatory. If you want to see what menu items are actually in that shortcut menu, you can use the ShowPopUp method of the CommandBar object.
Commandbars("Print Preview Popup").ShowPopup
However, this method will only show you the items in the menu that are visible in the context where it is used; some may be disabled and some may be hidden. Another method is to simply loop through all of the controls associated with the command bar and print them out or store them. The following code will print a list of the menu items, their ID value, and whether they are visible in the current context to the debug window.
Public Sub CbrMenuItems(CommandBarName As String)
Dim intLoop As Integer
Dim ctrl As Control
On Error Resume Next
For intLoop = 1 To CommandBars(CommandBarName).Controls.Count
Debug.Print CommandBars(CommandBarName).Controls(intLoop).Caption;
Debug.Print CommandBars(CommandBarName).Controls(intLoop).ID;
Debug.Print CommandBars(CommandBarName).Controls(intLoop).Visible
Next
End Sub
I included the On Error Resume Next line because not every commandbar control contains an ID property and if you try to print that property for a control that does not contain it, Access will raise an error.
Access will automatically enable/disable or hide the various controls in these commandbars based on the context in which it is being used, but occassionally you may want to customize this functionality.
I have a client that likes the ability to view their data in a datasheet view, allowing each user to customize their view of the data by hiding and repositioning columns. However, only certain individuals have permissions to delete records in these tables. I normally just set the forms Allow Deletions property to False in the Form_Load event for those individuals who do not have this permission, but when you do so, the "Delete Row" option is still displayed (disabled) in the Form Datasheet Row commandbar. To hide that control, I simply set that controls visible property to false using the following code.
CommandBars("Form Datasheet Row").Controls(2).visible = False
Note: don't forget to reset that property to True when you close or unload the datasheet.
In this example, the value 2 relates to the index value of the control within the commandbar. Another alternative, if you don't know the index value of the control or if you want your code to be more readable, is to use the FindControl method of the CommandBar object using a syntax of:
Commandbars(CommandBarName).findcontrol([type], [Id], [Tag], [Visible], [Recursive])
This method returns a control object which you can set to a control variable:
Set ctrl = Commandbars().FindControl(ID:=644)
that you can use within your code to reference various properties of the control. To hide the "Delete Row" option from the "Form Datasheet Row" popup, you would use:
Commandbars().FindControl(ID:=644).Visible = false
All of the built-in commandbar controls have an ID property which you can use to select a commandbar control.
Because you can control the visibility of commandbar controls through VBA and can also add new items to one of the built-in commandbars, you can gain complete control over the built in commandbars. CAUTION: if you modify commandbar control properties, you must ensure that you reset those properties or they will persist throughout the remainder of that Access session. I generally use the Form_Load and Form_Close events of my forms to make these modifications.
It is worth mentioning that, Referring to a commandbar in a subform must use the correct commandbar reference
referring directly to a datasheet form like
CommandBars("Form Datasheet Column").Controls(8).Visible = False
CommandBars("Form Datasheet Column").Controls(13).Visible = False
while referring to a datasheet form view in a sub form it should be like
CommandBars("Form Datasheet SubColumn").Controls(8).Visible = False
CommandBars("Form Datasheet SubColumn").Controls(13).Visible = False
referring here to the full code that may be in on load event
Private Sub Form_Load()
'Dim commandBar As Office.commandBar
If Globals.UserAccess("FrmPAChargesMatrixExtend") = False Then
MsgBox "You don't have access!"
DoCmd.Close acForm, "FrmPAChargesMatrixExtend", acSaveNo
Else
DoCmd.OpenForm "FrmPAChargesMatrixExtend"
DoCmd.GoToRecord acActiveDataObject, "FrmPAChargesMatrixExtend", acFirst
DoCmd.Maximize
CommandBars("Form Datasheet SubColumn").Controls(8).Visible = False
CommandBars("Form Datasheet SubColumn").Controls(13).Visible = False
End If
'Set commandBar = CommandBars.Item("Form Datasheet SubColumn")
'For i = 1 To commandBar.Controls.Count
''MsgBox i
' Set ctl = commandBar.Controls.Item(i)
'
' If ctl.Caption = "&Delete" Then
' '_
' 'Or ctl.Caption = "Hide &Fields" _
' 'Or ctl.Caption = "&Unhide Fields" _
' 'Or ctl.Caption = "Free&ze Fields" _
' 'Or ctl.Caption = "Unfreeze &All Fields" Then
' ctl.Visible = False
' End If
' Next
End Sub
In my next article, I'll discuss how to create your own commandbars and how to modify existing commandbars to add your own functionality to the existing.
As stated in the first article, 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 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
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
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])
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])
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
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
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”
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”)
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()”
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’)”
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
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
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
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
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
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
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
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.