12
\$\begingroup\$

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
\$\endgroup\$
5
\$\begingroup\$

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
\$\endgroup\$
  • 1
    \$\begingroup\$ I think at that point, I'll just do away with all of the intermediate routines. Thanks! \$\endgroup\$ – RubberDuck Jul 24 '14 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.