Home » Access Tips And Tricks » Adding Shortcut Menus to Your Applications

As Access developers we all know how important right-click menus are; we use them constantly as we develop our applications, but did you know that you can create your own custom shortcut (right-click)Sample Shortcut Menu menus in your applications?  Prior to A2007, you could do this by simply clicking Tools => Customize and then create your own menus, but when A2007 was distributed this feature was removed.  For a number of years, the only way to create custom shortcut menus was to write the code yourself.  This is not difficult, but understanding and utilizing all of the properties of the Commandbar object is complex.  So, about two years ago, I started work on an application (the Access Shortcut Tool) which would make it easy for me to create my own shortcut menus and modify the menus built-in to Microsoft Access.  During this process, I wrote several articles about working with the Commandbars collection, describing how to create your own, and providing information on how to manipulate the built-in menus.

These articles provide lots of information on creating shortcut menus and adding to the built-in menus, and will provide all you need to create your own menus;  the good new is, you don’t have to.  I have finally completed development of Version 1 of the Access Shortcut Tool, which provides the ability to:

 1.  Create your own shortcut menus.  You can easily create a new menu,  add custom or built-in controls to your menu, define the image you want to assign to a button control on your menu, and call user defined procedures when a menu selection is made.  Once created, you can easily edit your controls to change any of the controls properties and can re-position controls within your menus.

2.  Add controls to built-in menus.  I frequently add controls to the built-in form and datasheet menus but don’t want these to be permanent additions.  The Shortcut Tool allows me to create these as temporary controls and then generate the VBA code which can then be copied into my applications to add controls to these menus at run-time.

3.  Copy menus and the associated code from your older applications.  You can do this using the External Data => Import dialog box, but using that method, you are forced to copy all of the menus from that previous application and if there is custom code associated with those control, you have to find that code and import it separately.  Using the Access Shortcut Tool, you copy menus one-at-a-time and can copy the code associated with the single menu from the older application into your current application.

4.  Display the name of the currently selected shortcut menu.  There are over 200 built-in menus and determining the name and index number of the currently displayed menu is difficult, but the Access Shortcut Tool provides a way to do this, making it easy to identify the menu you want to modify or copy.

5.  Copy Access’s built-in menus for use in run-time applications.  Did you know that Microsoft disables the built-in right-click menus when your application is distributed as a run-time application?  It does, but still allows you to assign shortcut menus to the Shortcut Menu Bar property of controls, form, and reports.  Unfortunately, you cannot simply use the names associated with the built-in menus, you must create your own.  The Access Shortcut Tool provides a way to copy the complex built-in menus and give them a new name.  You can then assign this new menu to the Shortcut Menu Bar property of controls, forms, or reports in your run-time applications.

A free, limited use, trial version of the Access Shortcut Tool can be downloaded from here.

2 thoughts on “Adding Shortcut Menus to Your Applications

  1. Stu Gilman says:

    Hi Dale,

    I downloaded the trial version of your shortcut bar pgm and wasn’t sure how I could use this, as it seems that I have to rebuild the menu each time I open/close the form using the code you gave me:

    Public Sub DatasheetPopups(frm As Form, ColCount As Integer)

    Dim strMenu As String

    If (frm.SelHeight = 0) And (frm.SelWidth = 0) Then

    strMenu = “Form Datasheet Cell”

    ElseIf (frm.SelHeight = frm.RecordsetClone.RecordCount + 1) And (frm.SelWidth = 1) Then

    strMenu = “form datasheet ERI”

    ElseIf frm.SelWidth = ColCount Then

    strMenu = “form datasheet row”

    End If

    If Len(strMenu) > 0 Then

    CommandBars(strMenu).ShowPopup

    Else
    MsgBox “invalid selection”
    End If

    End Sub

    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 BuildFormDatasheetERI()

    Dim cbr As CommandBar
    Dim ctrl0 As Object

    ‘ Remove all controls from Datasheet ERI Toolbar

    RemoveControlsFromDatasheetERI

    On Error Resume Next

    ‘ Rebuild the Pop-Up Bar controls

    Set cbr = CommandBars.Add(“Form Datasheet ERI”, msoBarPopup, False, False)

    With CommandBars(“Form Datasheet ERI”).Controls

    Set ctrl0 = .Add(Type:=1, Temporary:=-1)
    With ctrl0
    .Caption = “Copy”
    .OnAction = “=CopyColumn()”
    .Visible = -1
    .Enabled = -1
    .BeginGroup = -1
    .TooltipText = “Copy Column”
    .Width = 189
    End With

    Set ctrl0 = .Add(Type:=1, Temporary:=-1)
    With ctrl0
    .Caption = “Sort Ascending”
    .OnAction = “=SortAscDatasheetColumn()”
    .Visible = -1
    .Enabled = -1
    .BeginGroup = -1
    .TooltipText = “Sort Ascending”
    .Width = 189
    End With

    Set ctrl0 = .Add(Type:=1, Temporary:=-1)
    With ctrl0
    .Caption = “Sort Descending”
    .OnAction = “=SortDescDatasheetColumn()”
    ‘ .FaceId = 14468
    .Visible = -1
    .Enabled = -1
    .BeginGroup = 0
    .TooltipText = “Sort Descending”
    .Width = 189
    End With

    Set ctrl0 = .Add(Type:=1, Temporary:=-1)
    With ctrl0
    .Caption = “Hide Column(s)”
    .OnAction = “=HideDatasheetColumn()”
    .Visible = -1
    .Enabled = -1
    .BeginGroup = -1
    .TooltipText = “Hide Column”
    .Width = 189
    End With

    Set ctrl0 = .Add(Type:=1, Temporary:=-1)
    With ctrl0
    .Caption = “Unhide Column(s)”
    .OnAction = “=UnhideDatasheetColumn()”
    .FaceId = 14468
    .Visible = -1
    .Enabled = -1
    .BeginGroup = 0
    .TooltipText = “Unhide Column(s)”
    .Width = 189
    End With

    Set ctrl0 = .Add(Type:=1, Temporary:=-1)
    With ctrl0
    .Caption = “Column Width”
    .OnAction = “=DatasheetColumnWidth()”
    .Visible = -1
    .Enabled = -1
    .BeginGroup = -1
    .TooltipText = “Column Width”
    .Width = 189
    End With

    Set ctrl0 = .Add(Type:=1, Temporary:=-1)
    With ctrl0
    .Caption = “Freeze Column”
    .OnAction = “=FreezeDatasheetColumn()”
    .Visible = -1
    .Enabled = -1
    .BeginGroup = -1
    .TooltipText = “Freeze Column”
    .Width = 189
    End With

    Set ctrl0 = .Add(Type:=1, Temporary:=-1)
    With ctrl0
    .Caption = “Unfreeze All Columns”
    .OnAction = “=UnFreezeDatasheetColumns()”
    .Visible = -1
    .Enabled = -1
    .BeginGroup = -1
    .TooltipText = “Unfreeze All Columns”
    .Width = 189
    End With

    End With

    Exit_BuildFormDatasheetERI:
    Exit Sub

    Err_BuildFormDatasheetERI:

    gstrtitle = “Sub BuildFormDatasheetERI”
    gstrMsg = (“Error ” & Trim(Str(Err)) & “: ” & Error(Err))
    DoCmd.Beep
    MsgBox gstrMsg, vbExclamation, gstrtitle
    Resume Exit_BuildFormDatasheetERI

    End Sub

    Public Sub RemoveControlsFromDatasheetERI()

    On Error Resume Next

    With CommandBars(“Form Datasheet ERI”)

    .Controls(“Copy”).Delete
    .Controls(“Sort Ascending”).Delete
    .Controls(“Sort Descending”).Delete
    .Controls(“Hide Column(s)”).Delete
    .Controls(“Unhide Column(s)”).Delete
    .Controls(“Column Width”).Delete
    .Controls(“Freeze Column”).Delete
    .Controls(“Unfreeze All Columns”).Delete

    End With

    End Sub

    Public Function CopyColumn()

    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl

    DoCmd.RunCommand acCmdCopy

    End Function

    Public Function SortAscDatasheetColumn()

    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl

    DoCmd.RunCommand acCmdSortAscending

    End Function

    Public Function SortDescDatasheetColumn()

    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl

    DoCmd.RunCommand acCmdSortDescending

    End Function

    Public Function DatasheetColumnWidth()

    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl

    DoCmd.RunCommand acCmdColumnWidth

    End Function

    Public Function HideDatasheetColumn()

    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl

    DoCmd.RunCommand acCmdHideColumns

    End Function

    Public Function UnhideDatasheetColumn()

    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl

    DoCmd.RunCommand acCmdUnhideColumns

    End Function

    Public Function FreezeDatasheetColumn()

    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl

    DoCmd.RunCommand acCmdFreezeColumn

    End Function

    Public Function UnFreezeDatasheetColumns()

    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl

    DoCmd.RunCommand acCmdUnfreezeAllColumns

    End Function

    I really like the interface, but couldn’t seem to save any of the settings. I assume that is because it is the trial version. If I was to buy this, can I setup the exact calls to the the functions shown above and permanently store the popup bar in all of my applications, and set it as the default Shortcut Menu for the datasheet forms?

    1. Dale says:

      Stu,

      The code I originally gave you added two controls to a built-in menu at run time, and removed them before the application quit. For working with built-in menus, I always do it this way to avoid carrying new menu options over into other applications.

      However, when working with your own built-in menus, when you create the item, make sure you do not check the “Temporary” checkbox. This will make the menu permanent, in the current database.

Leave a Reply

Your email address will not be published. Required fields are marked *

Name *
Email *
Website