When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and removed the user interface for creating commandbars. This resulted in a common misconception that all CommandBars have been deprecated (eliminated). The truth is that you can no longer create CommandBar MENUS
starting with Office 2007. However, you can still create and use pop-up menus, which are sometimes referred to as "right-click" or "shortcut" menus, using VBA. This series is all about these types of menus; I will use these two terms interchangeably in the remainder of this article.
Part 1 of this series (Understanding and Using Commandbars
) describes techniques for exploring and using the CommandBar object. Part 2 of the series (Creating your Own
) provides a more detailed discussion of the CommandBar and CommandBar Control object models, and demonstrates how to create your own shortcut menus.
While working on a new application, I found a need to not only develop my own right-click menus, but to augment and replace several of the existing, built-in, menus. In this article, you will learn how to:
1. determine the name of a shortcut menu which pops up when you right-click on an object or control.
2. disable a shortcut menu
3. hide the controls on a menu
4. and add your own controls to the menu.
The attached file contains all of the code used and several examples of how to perform the tasks described in this article.Commandbar-III---article.accdb
There are nearly 200 CommandBars built into Microsoft Access, and many more built into the other products in Microsoft Office. The first step in modifying one of these built-in menus is discovering its name. There are two ways to refer to a shortcut menu: name and index. Unfortunately, there is no built-in mechanism to determine the name or the index of a particular shortcut menu. The easiest way I've found to do this is to add an option to the bottom of each shortcut menu which, when clicked, will print the name and index of the current shortcut menu into the immediate window. If you add the code below to your application and run it, it will create a new menu option as the last entry of most (but not all) of the Access pop-up menus.
Public Sub WhatsThis()
Dim cbr As CommandBar
Dim btn As CommandBarButton
On Error GoTo ProcError
For Each cbr In Application.CommandBars
Set btn = cbr.Controls.Add(1, , , , True)
btn.Caption = "What's This"
btn.OnAction = "WhatsThisMenu"
btn.BeginGroup = True
On Error Resume Next
Set btn = Nothing
Set cbr = Nothing
Public Sub WhatsThisMenu()
Debug.Print "Name: ";CommandBars.ActionControl.Parent.Name
Debug.print "Index: ";commandbars.ActionControl.Parent.Index
msgbox "Name : " & Commandbars.ActionControl.Parent.Name & vbcrlf _
& "Index: " & Commandbars.ActionControl.Parent.Index
Code Block #1
This code loops through the set of all commandbars (built-in and yours) in your application and appends an option to the bottom of each menu. When you right click in Access (either when your application is running, or in design view) you will be able to quickly identify the name of the pop-up menu that is displayed.
Manipulating Built-in Commandbars:
When you refer to a shortcut menu, it is best to use the name, rather than the index associated with the menu. Although many of the CommandBars have fixed index numbers, when you create new shortcut menus, they can be inserted at odd locations in the CommandBars collection and can actually be inserted between other Commandbars, so using the Name property rather than the index is critically important (see note in 1b for exception).
1. Hiding CommandBars
: Sometimes, in your applications, you will want to hide the default shortcut menus which popup when you right click in various locations on your forms and reports. There are several ways to accomplish this:
a. If you are working on a form, you can simply set the forms "Shortcut Menu" property to NO. However, doing so will disable all of the shortcut menus associated with that form. It will also cause the drop down button that appears in the upper right corner of datasheet column headers to disappear. Notice the subtle difference between frm_Numbers_Datasheet1 and frm_Numbers_Datasheet in the image below.
b. You can also hide specific CommandBars by setting their "Enabled" property to False. The example below hides the shortcut menus which pop-up when you click on a form header or within the body of a form, but not the ones related to controls. I call this code in the Open event of my Splash form and reverse it in the Close event of that form when the application closes.
commandbars(127).Enabled = False
commandbars("Form View Popup").Enabled = False
Code Block #2
There are three CommandBars in Access which do not have Names, and which must be referred to by their index #. The one related to the top border of a form or report (where the Close button, Caption, and min, max, and close buttons are located) is #127.
- Don't forget to set these back to TRUE when you close your forms or these will not be enabled during design mode.
c. A third method to hide the controls in a Commandbar is to set the Visible property of the controls to False. This technique can be used to hide specific items from a built-in menu while still displaying some or all of the built-in options and adding application specific options. I use the following subroutine to hide/reveal all of the built-in controls in a shortcut menu in one step.
Public Sub HideBar(BarName As String, Optional IsHidden As Boolean = True)
Dim ctrl As Object 'CommandBarControl '
On Error GoTo ProcError
For Each ctrl In CommandBars(BarName).Controls
If ctrl.BuiltIn = true then ctrl.Visible = Not IsHidden
Select Case Err.Number
Case 5, -2147024809
MsgBox "Invalid commandbar name"
Debug.Print Err.Number, Err.Description
Code Block #3
When you do this, the short-cut menu will continue to display but will not display any of the built-in controls (shown below). Short-cut menus are context sensitive and
you might think that Access would override this setting and reset the visible property of the controls to true; I have never experienced this behavior.
2. Editing Built-in CommandBars:
To add application specific menu items (controls) to a built-in shortcut menu, you can use code similar to the example provided in Part II of this series. These controls can be added at any point in an application, but I prefer to call the procedure that adds these controls during my application startup process. If you create these as temporary controls (the True argument in the Add method below), they will automatically be deleted when you close your application.
Dim ctrl As Object 'CommandBarControl
With CommandBars("Form Datasheet Row")
Set ctrl = .Controls.Add(1, , , , True)
ctrl.Caption = "Display &Details"
ctrl.OnAction = "SomeFormDisplayRecordDetails"
ctrl.Visible = False
ctrl.Enabled = true
Code Block #4
This example adds a "Display D
etails" option to the Form Datasheet Row
shortcut menu and hides that control when it is created. Then, in the Form_Open event of the main form that will be using these controls, you can hide the built-in controls and display this user defined control:
HideBar "Form Datasheet Row"
Commandbars("Form Datasheet Row").Controls("Display &Details").Visible = -1
Code Block #5
When the user right clicks on the row selector in your datasheet, this one menu option will be displayed and if the user clicks on that option, the subroutine "SomeFormDisplayRecordDeta
ils" will be called. In that procedure you would determine which record is selected and open a details form specific to that record. That code might look like:
Public Sub SomeFormDisplayRecordDetails
Dim strCriteria as string
strCriteria = "[ID] = " & Forms!MainForm.subFormControl.Form.txt_ID
Docmd.Openform "SomeFormDisplayDetails", , , strCriteria
Code Block #6
*Note: Don't forget to reverse that process when you close that form. If you forget, the next time you try to right click on a datasheet row selector, you will still see this menu option rather than the standard menu options.
HideBar "Form Datasheet Row", False
Commandbars("Form Datasheet Row").Controls("Display &Details").Visible = False
Code Block #7
Using your own shortcut menus:
In many instances, it is simplier to create and use your own shortcut menus than to modify the built-in menus. Although creating these shortcut menus can mean a tedious amount of coding, once you have code written that handles a specific funtionality, you can reuse that code in all of your applications. Once you have created your own menu, you can override many of the built-in menus by setting the Shortcut Menu Bar property of a form, report, or control to the name of your shortcut menu. This method provides great flexibility as it give you the ability to present only those options you want your users to see when they are viewing a form or editing a control.
Unfortunately, forms in datasheet view have no mechanism to allow you to replace the right-click menus which popup based on the context of what has the focus when the mouse is right-clicked. To replace the Form Datasheet Row
and Form Datasheet Column
commandbars you can set the Shortcut Menu property of the form to NO, then use the Form_MouseUp event to trap the right mouse button and run a procedure to check whether an entire row or an entire column is selected. The following is an example of the code I use in my datasheet forms.
Option Compare Database
Dim strAction As String
Dim intDataShtColCnt As Integer
Private Sub Form_Open(Cancel as Integer)
'Determine the number of columns in the datasheet
intDataShtColCnt = DatasheetColumnCount(Me)
Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Button = acRightButton Then DatasheetPopups me, intDataShtColCnt
Private Sub txt_SomeControl_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
'It is necessary to put this code in the MouseUp event of each control in the datasheet
'which you want to be able to use the "form datasheet cell" shortcut menu
If Button = acRightButton Then CommandBars("form datasheet cell").ShowPopup
Code Block #8
Then, in a public code module, you will need the following two procedures:
Public Function DatasheetColumnCount(frm As Form) As Variant
Dim ctrl As Control
Dim intCtrlCount As Integer
DatasheetColumnCount = Null
If frm.CurrentView <> 2 Then Exit Function
For Each ctrl In frm.Section(acDetail).Controls
If ctrl.ControlType <> 100 Then
intCtrlCount = intCtrlCount + 1
DatasheetColumnCount = intCtrlCount
Public Sub DatasheetPopups(frm As Form, ColCount As Integer)
If (frm.SelHeight = frm.Count) And (frm.SelWidth = 1) Then
CommandBars("form datasheet column").ShowPopup
ElseIf frm.SelWidth = ColCount Then
CommandBars("form datasheet row").ShowPopup
Code Block #9
The first of these procedures simply counts the number of non-label controls in the detail section of the form that is passed to it. This will be the value returned by frm.SelWidth if the row selector is clicked. The second procedure checks to see whether the number of rows selected is equal to the number of records in the form (column is selected) or if number of columns selected is equal to the number of columns in the datasheet.
To replace the Form Datasheet Cell
menu, you can either set each of the controls Shortcut Menu Bar property to a shortcut menu which you have developed, or can use the MouseUp event of each of those controls (see Code Block #8) to check for the right mouse click and use the Commandbars("form datasheet cell").showpopup method.
Many of you will develop applications for users who don't have Access installed on their computers, and will have to deploy the application via the Access run-time. Unfortunately, Microsoft has disabled the built-in menus in the Run-time version of Access, so your users will not have access to the built-in shortcut menus unless you implement the techniques described in this article.