Creating ShortCut (RightClick) Menus in Access

Question

I'm writing a tool to create shortcut menus for an application that I maintain built on the Access runtime. I compile this app into an *.accde file, so much to my chagrin, all right click menus are disabled.

After some research, I've been able to develop this tool to add the menus to the database. This code is called during development, not at runtime.

I'm already seeing a lot of duplication, and am not sure what to do about it. So, I figured I'd stop and get some feedback before I started building all of the various menus that I'm going to need.

Note: There is not a built in Enumeration of all of the different button types. There are thousands of them and I'm adding them to a self defined enum as I need them.

Option Explicit

Private Enum ButtonControls
    bcCopy = 19
    bcCut = 21
    bcPaste = 22
    bcSortSmallToLarge = 210
    bcSortSortLargeToSmall = 211
    bcDelete = 478
    bcNewRecord = 539
    bcRowHeight = 541
    bcDeleteRecord = 644
End Enum

Private Sub CreateFormDatasheetClipboard()
    Dim menu As Office.CommandBar
    Set menu = CommandBars.Add("FrmDsClipboard", msoBarPopup, False)

    With menu.Controls
        .Add msoControlButton, bcCut
        .Add msoControlButton, bcCopy
        .Add msoControlButton, bcPaste
    End With
    Set menu = Nothing
End Sub

Private Sub CreateFormDataSheetCopyOnly()
    Dim menu As Office.CommandBar
    Set menu = CommandBars.Add("FrmDsCopyOnly", msoBarPopup, False)

    menu.Controls.Add msoControlButton, bcCopy

    Set menu = Nothing
End Sub


Private Sub CreateFormDataSheetRow()
    Dim menu As Office.CommandBar
    Set menu = CommandBars.Add("FrmDsRow", msoBarPopup, False)

    With menu.Controls
        .Add msoControlButton, bcNewRecord
        .Add msoControlButton, bcDeleteRecord
        .Add msoControlButton, bcCut
        .Add msoControlButton, bcCopy
        .Add msoControlButton, bcPaste
        .Add msoControlButton, bcRowHeight
    End With

    Set menu = Nothing
End Sub

Private Sub CreateFormDataSheetRowClipboardOnly()
    Dim menu As Office.CommandBar
    Set menu = CommandBars.Add("FrmDsRowClipboardOnly", msoBarPopup, False)

    With menu.Controls
        .Add msoControlButton, bcCut
        .Add msoControlButton, bcCopy
        .Add msoControlButton, bcPaste
    End With

    Set menu = Nothing
End Sub

Private Sub RemoveAllCustomMenus()
    Dim cbar As CommandBar
    For Each cbar In CommandBars
        If Not cbar.BuiltIn Then
            cbar.Delete
        End If
    Next
End Sub

Private Sub CreateAllCustomMenus()
    RemoveAllCustomMenus
    CreateFormDatasheetClipboard
    CreateFormDataSheetCopyOnly
    CreateFormDataSheetRow
    CreateFormDataSheetRowClipboardOnly
End Sub

Xiaoy312 · Accepted Answer · 2014-07-24 20:29:40Z

5

As you already noticed, all the CreateSomething methods have the same repeating pattern. We can extract this pattern and encapsulate it in a method for reuse :

Private Sub CreateCommandBar(name As String, ParamArray buttonIDs() As Variant)
    Dim menu As Office.CommandBar
    Set menu = CommandBars.Add(name, msoBarPopup, False)

    For Each ID In buttonIDs
        menu.Controls.Add msoControlButton, i
    Next

    Set menu = Nothing
End Sub

And, CreateFormDataSheetRowClipboardOnly could be written as :

Private Sub CreateFormDataSheetRowClipboardOnly()
    CreateCommandBar "asd", bcCut, bcCopy, bcPaste
End Sub

answered Jul 24, 2014 at 20:29

Xiaoy312

3,13617 silver badges18 bronze badges

1

\$\begingroup\$ I think at that point, I'll just do away with all of the intermediate routines. Thanks! \$\endgroup\$

RubberDuck
– RubberDuck

2014-07-24 20:44:46 +00:00
Commented Jul 24, 2014 at 20:44

Add a comment |

Stack Exchange Network

Creating ShortCut (RightClick) Menus in Access

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Creating ShortCut (RightClick) Menus in Access

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions