<

Go Premium for a chance to win a PS4. Enter to Win

x

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

Published on
15,988 Points
5,088 Views
4 Endorsements
Last Modified:
Awarded

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.
4
Comment
Author:Dale Fye
  • 2
3 Comments
 
LVL 49

Author Comment

by:Dale Fye
Thanks, for the nice comments.

I didn't realize there was a way to center the text, or I certainly would have used it.
0
 
LVL 49

Author Comment

by:Dale Fye
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".
0
 
LVL 66

Expert Comment

by:Jim Horn
Excellent article.  Voted Yes.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month