Macro for right click menu

[fat controller]

I am trying to create a right click menu to allow users of a database app to be able to right click and then select Copy or Paste, and I am trying to follow this article - however, when I click on create macro I do not get the same view as shown in that article - the view I get is as per the attached file.

I have tried on the ribbon to see if I can find show/hide Macro Names, but can find it nowhere - can anyone nudge me in the right direction please?

 

[CJ_London]

what version of access are you using? The article is for 2007, looks like your attachment is 2010.

Have a look at this link instead

https://www.youtube.com/watch?v=G0OetcYcbM8

 

[fat controller]

I have 2010 on three of my PC's including my work laptop, and 2013 on one desktop PC, I can use whichever one is going to be best/easiest?

Apologies, I thought that features in 2007 would have been carried forward into 2010 and 2013 - will take a look at the vid now - cheers

 

[fat controller]

I have sort of got somewhere, but need some more help please. I have put the following code in as a Private Sub at the start of the VBA for the form;

PrivateSub ShortcutMenu()
Dim MenuName As String
Dim CB As CommandBar
Dim CBB As CommandBarButton
MenuName = "vbaShortcutMenu"
On Error Resume Next
Application.CommandBars(MenuName).Delete
On Error GoTo 0
Set CB = Application.CommandBars.Add(MenuName, msoBarPopup, False, False)
Set CBB = CB.Controls.Add(msoControlButton, 19, , , True)
CBB.Caption = "Copy..."
CBB.FaceId = 19
Set CBB = CB.Controls.Add(msoControlButton, 22, , , True)
CBB.Caption = "Paste..."
CBB.FaceId = 1436
Set CBB = CB.Controls.Aff(msoControlButton, 11725, , , True)
CBB.Caption = "Export to Word..."
CBB.FaceId = 42
Set CB = Nothing
Set CBB = Nothing
End Sub

I have then put ShortcutMenu in on the OnLoadEvent of the form, and vbaShortcutMenu in the Shortcut Menu Bar property on the Other tab.

Since doing so, the form will not open and I get a Runtime Error 2501; Removing 'Shortcutmenu' from the forms OnLoad event makes no difference. Removing the Private Sub allows the form to open as normal, however whenever right clicked I now (understandably) get a message that vbaShortcutMenu cannot be found.

Am I putting the Private sub in the wrong place?

EDIT - perhaps I should have posted this in the VBA section?

 

[fat controller]

OK - I made a slight change to the code, and now I am getting a different error:

Compile Error:
User-defined type not defined

Dim MenuName As String
 
[B]Dim CB As CommandBar[/B]
 
Dim CBB As CommandBarButton
MenuName = "vbaShortcutMenu"
On Error Resume Next
Application.CommandBars(MenuName).Delete
On Error GoTo 0
Set CB = Application.CommandBars.Add(MenuName, msoBarPopup, False, False)
Set CBB = CB.Controls.Add(msoControlButton, 19, , , True)
CBB.Caption = "Copy..."
CBB.FaceId = 19
Set CBB = CB.Controls.Add(msoControlButton, 22, , , True)
CBB.Caption = "Paste..."
CBB.FaceId = 1436
Set CBB = CB.Controls.Aff(msoControlButton, 11725, , , True)
CBB.Caption = "Export to Word..."
CBB.FaceId = 42
Set CB = Nothing
Set CBB = Nothing

The debugger is highlighting the Dim CB As CommandBar line.

 

[CJ_London]

not sure where you are going wrong - what is error 2501?

there should be a space here

PrivateSub

I assume your type and faceid's are correct

It is probably because you are setting the shortmenu to the control before the shortcut menu exists

So try removing the shortcut menu from the controls and assigning it in VBA (myControl.shortcutmenubar="vbashortcutmenu") after you have created the shortcut menu, or alternatively it may work if you create the shortcut menus in the form open event.

 

[fat controller]

OK, I have done a bit more digging around and discovered that the problem appeared to be a reference issue. The search results suggested that I should reference Microsoft Office 10.0 Object Library, however it did not appear in the list.... 14.0 & 15.0 did though, and as 14.0 was already selected I ticked the box for 15.0 and we now almost have lift off...

Now, when the form opens, I get no error, and when I right click I get my custom menu - the icons are right too, however the Copy and Paste ones are greyed out.

As far as I know the policy ID's and Face ID's are spot on (I downloaded the developer resource spreadsheet to get the policy ID's and also downloaded an Add-in that shows the FaceID's when you hover a mouse pointer over the desired icon)

Is there a setting somewhere that is disabling shortcut menus?

EDIT - Ignore this utter rubbish above, I was clearly in dim-witted mode. The Copy was greyed out as I had not highlighted anything to copy... and the Paste was greyed out as I hadn't copied anything for it to paste!

All working now - thanks for the link, as that took me through it step by step.