X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Getting Names Of Built-in Context Menus, Access 2007    
post Sep 26 2013, 03:12 PM

Dungeon Cleaner
Posts: 1,515
Joined: 16-June 07
From: Banana Republic

Since 2007, there has been no built-in way of seeing menus and therefore, it can be like picking needle in a haystack if you want to customize shortcut menus by adding your controls to built-in menus.
urthermore, there are different built-in menus for different context; adding your custom control to single built-in menu may not be enough if you want to be available in various context. Therefore, you must track down all possible menus to ensure that your users can always see your custom controls. This make the task too difficult.
The code makes it easy to find out which shortcut menu (as well other menus) that you want to get the name of and also add it to a table so that you have a easy list of reference for you to be able to add your custom controls.
How does it work?
Essentially, it simply adds three controls to all CommandBars where permitted at the bottom as shown here:
Attached File  Shortcutmenu5.png ( 15.32K )Number of downloads: 22

Those controls are marked as temporary so that next time you start up Access, those will not appear again unless you run the setup again which make it ideal for you to gather the menu you want to manage during your development.
The "What's my name?" returns a message showing you the name of the menu:
Attached File  ShortcutMenu2.png ( 7.24K )Number of downloads: 9

The "Add me to the table" will display an input box asking you to give an arbitrary name to associate the menu with.
Attached File  ShortcutMenu3.png ( 6.82K )Number of downloads: 8

The menu is then added to the table:
Attached File  ShortcutMenu4.png ( 12.62K )Number of downloads: 7

You now have a listing of all menus that you can now use code in your application startup to loop over and add your custom controls to each of those menu and ensure that same custom control will appear for all menus of the same menu group.
The code for assisting with discovering the name and storing the table name is given below. The code to add your custom control is left to you to develop. I hope this is useful to other developers.
Option Compare Database
Option Explicit
Private Const msoControlButton As Long = 1
Private Const msoControlPopup As Long = 10
Public Sub SetupHelper()
On Error GoTo ErrHandler
    Dim cmd As office.CommandBar
    Static bolRan As Boolean
    On Error Resume Next
    CurrentDb.Execute "CREATE TABLE tblMenus(MenuID AUTOINCREMENT PRIMARY KEY, CommandBarName VARCHAR(255) NOT NULL, CommandBarIndex INT NOT NULL, MenuGroup VARCHAR(255) NOT NULL, UNIQUE (CommandBarName, CommandBarIndex, MenuGroup));", dbFailOnError
    On Error GoTo ErrHandler
    If bolRan Then Exit Sub
    bolRan = True
    For Each cmd In Application.CommandBars
        With cmd.Controls.Add(msoControlPopup, 1, "GetMenuName", , True)
            .BeginGroup = True
            .Caption = "My name is:"
        End With
        With cmd.Controls("My name is:").Controls.Add(msoControlButton, 1, "ShowMenuName", , True)
            .Caption = cmd.Name
            .OnAction = "=MsgBox(""" & cmd.Name & """, 64, ""My Name Is:"")"
        End With
        With cmd.Controls("My name is:").Controls.Add(msoControlButton, 1, "ShowMenuIndex", , True)
            .Caption = cmd.Index
            .OnAction = "=MsgBox(""" & cmd.Index & """, 64, ""My Index Is:"")"
        End With
        With cmd.Controls.Add(msoControlButton, 1, "AddMenuName", , True)
            .Caption = "Add me to the table"
            .OnAction = "=AddMenu(""" & cmd.Name & """, " & cmd.Index & ")"
        End With
    On Error Resume Next
    Exit Sub
    If Err.Number = -2147467259 Then
        Resume ResumeNext
    End If
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    End Select
    Resume ExitProc
End Sub
Public Function AddMenu(MenuName As String, MenuIndex As Long) As Boolean
On Error GoTo ErrHandler
    Static strMenuGroupName As String
    strMenuGroupName = InputBox("What menu group do you want to associate '" & MenuName & "' with?", "Add Menu", strMenuGroupName)
    CurrentDb.Execute "INSERT INTO tblMenus (CommandBarName, CommandBarIndex, MenuGroup) VALUES (""" & Replace$(MenuName, """", """""") & """, " & MenuIndex & ", """ & Replace$(strMenuGroupName, """", """""") & """);", dbFailOnError
    On Error Resume Next
    Exit Function
    Select Case Err.Number
        Case 3022
            MsgBox "The menu is already in the tblMenu with same menu group."
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    End Select
    Resume ExitProc
End Function

EDIT: Added mean to store the CommandBar's Index as some menu do not have name at all. It seems that index COULD be used as a way to identify the menu without the name but I don't know for a fact if the Index is guaranteed to never change for built-in menus.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 03:49 PM