How do I implement an event properly in a VBA Context-Menu?

Question

I try to call an Function via an CommandBarButton-Event. The Code looks finde so far, I also get no Error, but nothing happens. What am I doing wrong? My Code looks like this:

Private Sub Main()
    ContextMenu1
    CommandBars("MyListControlContextMenu").ShowPopup
End Sub

Private Sub ContextMenu1()
    Dim MenuItem As CommandBarPopup
    On Error Resume 
    NextCommandBars("MyListControlContextMenu").Delete
    On Error GoTo 0

    With CommandBars.Add(Name:="MyListControlContextMenu", Position:=msoBarPopup, MenuBar:=False, Temporary:=True)
        Set MenuItem = .Controls.Add(Type:=msoControlPopup)
        With MenuItem
            .Caption = "main"
            Dim MenuButton As CommandBarButton
            Set MenuButton = MenuItem.Controls.Add(Type:=msoControlButton)
            With MenuButton
                .Caption = "sub"
                .OnAction = "Test1"
            End With
        End With
    End With
End Sub

Private Sub Test1()
    MsgBox "test", vbOKOnly, "test"
End Sub

Is that a cut and paste, because you have errors in the format. `EndWith` should be `End With` — Fionnuala, Jul 30 '14 at 10:40
no its a simplified version, but the MenuButton construction and the Test1() is the same. — Raymond Osterbrink, Jul 30 '14 at 10:41
You have `Whith MenuButton` I think the errors are being hidden from you, because that code does not compile. — Fionnuala, Jul 30 '14 at 10:47
oh, i see. but this error also is only on stackoverflow, in my original code its not. As i said before, my code compiles without errors, but my Sub Test1() does not get called, when clicking the CommandBarButton — Raymond Osterbrink, Jul 30 '14 at 10:50
Okay, I am lost. It is not the way I would do things in MS Access. — Fionnuala, Jul 30 '14 at 11:02
@Fionnuala it's copied from this question, it seems. http://stackoverflow.com/questions/770425/how-to-add-a-menu-item-to-the-default-right-click-context-menu — Mark C., Jul 30 '14 at 11:48
@OverMind Thanks, so not relevant to MS Access, or even to this year. — Fionnuala, Jul 30 '14 at 11:50

score 2 · Accepted Answer · answered Jul 30 '14 at 14:36

It seems like the correct syntax for MS-Access 2010 VBA when implementing an event inside a contextmenu looks like this:

Sub MyContextMenuConstructor()
    ...
    With MenuButton
        .Caption = "myCaption"
        .OnAction = "=MyFunction('" & myParameter & "')"
    End With
    ...
End Sub

Private Function MyEvent(myInputParameter As String)
    MsgBox myInputVariable, vbOKOnly
End Function

Remember:

- What ever called by the Event, needs to be a Function, no Sub; 
- It needs to be called with brackets, even if you do not pas a parameter; 
- When you call the Function, you must have an '=' before its name and you have to have an apostrophe before and after your Parameter. Also allways place a Whitespace between '&' and '"', since VBA doesn't seem to be that clever.

How do I implement an event properly in a VBA Context-Menu?

1 Answers1