Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

CommandBars (Part III) - Using Built-in Shortcut Menus

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Published:
Updated:

Preface:

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.
 

Introduction:

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

Commandbar Discovery:
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
                              
                      NextBar:
                          Next
                          
                      ProcExit:
                          On Error Resume Next
                          Set btn = Nothing
                          Set cbr = Nothing
                          Exit Sub
                          
                      ProcError:
                          Resume NextBar
                          
                      End Sub
                      
                      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
                      
                      End Sub
                      
                          

Open in new window


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.
Numbers-Datasheet.jpg
Image #1
     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

Open in new window


Code Block #2

  1. *Notes:
    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.
  2. 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
                          Next
                          
                      ProcExit:
                          Exit Sub
                      
                      ProcError:
                          Select Case Err.Number
                              Case 5, -2147024809
                                  MsgBox "Invalid commandbar name"
                              Case Else
                                  Debug.Print Err.Number, Err.Description
                          End Select
                          
                      End Sub

Open in new window


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 andShowPopupNoVisibleControls.jpg
Image #2
 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 
                      End With

Open in new window


Code Block #4
This example adds a "Display Details" 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

Open in new window


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 "SomeFormDisplayRecordDetails" 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
                      
                      End Sub

Open in new window


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

Open in new window


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
                      Option Explicit
                      
                      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)
                          
                      End Sub
                      
                      Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
                      
                          If Button = acRightButton Then DatasheetPopups me, intDataShtColCnt
                          
                      End Sub
                      
                      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
                          
                      End Sub

Open in new window


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
                              End If
                              
                          Next
                          DatasheetColumnCount = intCtrlCount
                          
                      End Function
                      
                      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
                          End If
                      
                      End Sub

Open in new window


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.
 

Run-time applications:

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.
5
11,427 Views
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT

Comments (3)

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks, for the nice comments.

I didn't realize there was a way to center the text, or I certainly would have used it.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks.  This is the first article I've written using the new editor, and I like the flexibility.  I was a little disappointed in the ability to size text other than using the couple of "styles".
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Excellent article.  Voted Yes.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.