Is it possible to Create a Custom Shortcut Menu in Access 2013?

[ItsMe]

I figured it had been long enough and thought I would grab an old MDB application and see if I could get it up and running as an accdb, using Access 2013 as the development machine. Everything seemed to be going OK, usual stuff ... then I noticed none of my custom shortcut menus were available. So these shortcut menus are the ones using the Command Bar and Buttons.



Long story short, it does not work in A2013. I tried creating them via code, importing them, building them with code in 2010 and opening with 2013, and maybe a couple of other things.

So, did I miss something? Is there another way to create a shortcut menu? Do I have to Group Macros and do something with Macros? Do I, simply, have to have my apps open via the Access 2010 runtime?

[ranman256]

Thanks to Microsoft, they changed all menus in Access ,excel, thus ruining our past menus. I hate the new access.
BUT you can still make hot keys, by putting the ampersand in front of a letter on a button, or label.
i have LOTS of these.

[CJ_London]

the way to build custom menus changed in 2007 - when you said you tried to build in code, did you use the right code?

here are a couple of links

https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

https://support.office.com/en-US/Art...0-3309aebf8a1a

[ItsMe]

Thanks to Microsoft, they changed all menus in Access ,excel, thus ruining our past menus. I hate the new access.
BUT you can still make hot keys, by putting the ampersand in front of a letter on a button, or label.
i have LOTS of these.

The CommandBar class is in the Office Object library and I can reference it. I, just, cannot associate it to the shortcut menu. If you can create the shortcut keys, in my eyes, that proves the class works.

Now, how do you associate it to the shortcut menu? I was looking at the MSDN documentation on the architecture of 2013 and it is different than 2010. I do not know how familiar I want to get with this new architecture. There is supposed to be a release of a new version of Office. It is code named Version 16 or something. I am very interested to see what Access will look like in the next version.

the way to build custom menus changed in 2007 - when you said you tried to build in code, did you use the right code?

I referenced the Microsoft Office Library and used the CommandBar class. In fact, after I was unsuccessful, I tried one of the snippets you linked to just to double check. The one thing I did not try is to create a Macro. I think the macro uses the same class. However, this may be missing link to the new shortcut menu.

I started my adventure on a W8.1 machine and Office 2013 ...

In order to get the code to work, you need to know the policy ID's of the menu item so you can assign the menu item to your Button. I could not find any documentation so I interrogated the application to retrieve and enumerate the policy ID's with their associated Caption.

Dim oCommandBar As CommandBar
Dim oCommandBarControl As CommandBarControl
'Open Recordset of table that has two columns and no records
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("A_A_tblShortcutMenu", dbOpenDynaset)
'Iterate all of the button things and enumerate them
    For Each oCommandBar In Application.CommandBars
        For Each oCommandBarControl In oCommandBar.Controls
            If oCommandBarControl.Type = msoControlButton Then
                rs.AddNew
                    'assign values to our new record
                    rs![Caption] = Replace(oCommandBarControl.Caption, "&", "")
                    rs![ID] = oCommandBarControl.ID    'This is the Policy ID
                rs.Update
            End If
        Next oCommandBarControl
    Next oCommandBar
    Set oCommandBarControl = Nothing
    
rs.Close
Set rs = Nothing

Dim strSQL As String
'Retrieve the records from our A_A_tblShortcutMenu table and make table
'a new one that is named tblShortcutMenu
strSQL = "SELECT A_A_tblShortcutMenu.ID, A_A_tblShortcutMenu.Caption " & _
         "INTO tblShortcutMenu FROM A_A_tblShortcutMenu " & _
         "GROUP BY A_A_tblShortcutMenu.ID, A_A_tblShortcutMenu.Caption;"
CurrentDb.Execute strSQL

Then I used various techniques to create a custom shortcut menu. The following code was successful in creating the Shortcut Menu. I cannot remember every scenario. But, some code created a menu name that I could see in the global options. If I selected it as a default and tried to use it, I got an error saying the macro was no good or something. I do not believe I ever saw the name of my menu as being available from within the form's properties.

        
    Dim cmbShortcutMenu As Office.CommandBar
    
    ' Create a shortcut menu named "CopyPaste.
    Set cmbShortcutMenu = CommandBars.Add("CopyPaste", msoBarPopup, False, True)
    
    ' Add the Copy command.
    cmbShortcutMenu.Controls.Add Type:=msoControlButton, ID:=19
    ' Add the Paste command.
    cmbShortcutMenu.Controls.Add Type:=msoControlButton, ID:=22
    
    ' Add the Find...  command.
    cmbShortcutMenu.Controls.Add Type:=msoControlButton, ID:=141
    
    Set cmbShortcutMenu = Nothing
    Set cmbRightClick = Nothing

Then, I started going back and forth between W7 machine with Office 2010 and W8.1 with Office 2013. I could create shortcuts in Access 2010 that could be used in Access 2010 but could not be used in Access 2013. I would get the could not find Macro or something error. IIRC, running the code in Access 2013 and trying to use the shortcut in Access 2010 did not work. I would not get an error while running the code in Access 2013 but there was not a menu available for use within Access 2010.

[ItsMe]

I had put this on the back burner for a while and decided to have another crack at it today. I started to go a little crazy trying to apply XML to an IRibbonUI object; trying to create a new Context Menu or edit a default Context Menu. Ultimately, what works is the code in post #4. There is only one little catch. You have to run the code before you can use the Shortcut Menu.

If you place the code in a function and call the function, the form's ShortcutMenuBar property will accept the name of the custom pop-up menu. That is, until the application is closed. When you restart the app, the shortcut menu will not work. You need to call the code before using the Right Click. I wondered if a Run Time error would cause the shortcut menu to fail and it still worked just fine after throwing a run time exception.

I tested it in W8.1 with Access 2013 and also W7 with Access 2016 RC/Preview.

Any of the following statements will add a button.

cmbShortcutMenu.Controls.Add Type:=msoControlButton, Id:=22
'or
cmbShortcutMenu.Controls.Add 1, 22
'or
cmbShortcutMenu.Controls.Add  msoControlButton, 22

[CJ_London]

You need to call the code before using the Right Click

not sure what you mean - once you have created a shortcut menu it is added to the db properties - there for all time until you delete it. To use it you just set the shortcut menu property (on the other tab) of the control to the name of your menu - I'm using 2010 and never had a problem.

[ItsMe]

not sure what you mean ...

When using > 2010, you need to call the code each time you launch the app. After closing the application and before using the Shortcut/Context/Pop-Up Menu, the code must be called. Otherwise, if a form's ShortcutMenuBar property references the name of a custom Shortcut Menu, you will receive a message explaining there was some type of error (when using the Right Click on that form).

In the past, I have used 2010 to import custom menus created with the 2003 GUI and have used the custom menus in apps running 2010. Running those same apps with the 2013 RT creates an error.