Right-Click Option Caption

[mikeymay]

I am using some code to add some options to the cell right click function

When a cell is right clicked, there will be a number of options shown, in the above these are EAST and MIDS.

When one of these options is clicked, I need to be able to return which one is clicked as there is a macro that will run when an option is selected and I need the text string of the option selected to be in memory so I can drop it into a list.


TIA

 

[Domenic]

See if this will work for you.

First, let's assume that EAST calls your macro called "MyMacro1", and MIDS calls your macro called "MyMacro2".

Now, in that same module, declare a module-level string variable to hold the name of the option selected, let's call it menuItemCaller. (Note: If this value is needed in a separate module, declare it as Public.)

Then, for both "MyMacro1" and "MyMacro2", add a line of code that assigns the name of the option selected to your variable menuItemCaller.

Then, when you want to drop it into a list, simply refer to the variable menuItemCaller.

Here's an example...

Option Explicit

Dim menuItemCaller As String

Sub MyMacro1()

    menuItemCaller = "EAST"
 
    'etc
    '
    '

End Sub

Sub MyMacro2()

    menuItemCaller = "MIDS"
 
    'etc
    '
    '
 
End Sub

Sub DropIntoList()

    MsgBox menuItemCaller & " was the last menu item clicked!", vbInformation
 
    'etc
    '
    '
    '
 
    'menuItemCaller = ""
 
End Sub

Hope this helps!

 

[mikeymay]

The items in the right click are dynamic and are defined from a list that is looped through.

So dependent on the cell that is right clicked, this list will be different.

Because of this, I am using one macro to be called upon any of the items being clicked but I need to know which item was clicked prior to the macro executing.

 

[Domenic]

In that case, maybe you can use a class module, which would allow you to handle multiple controls with one event handler. Here's an example that hopefully you can adopt and amend as needed. I've assumed that the items are listed in Column A, starting at cell A2, and are located on "Sheet1" within the workbook running the code. Note that in this example, addShortCutMenuItems() is used to add your items to the shortcut menu, and deleteShortCutMenuItems() is used to remove them from the same. And you would likely want to use the workbook open event to call addShortCUtMenuItems() when the workbook opens, and then use the workbook close event to call deleteShortCutMenuItems() when the workbook is closed.

1) Insert a class module (Visual Basic Editor >> Insert >> Class Module), and call it clsMenuItem...

Option Explicit

Public WithEvents menuItem As Office.CommandBarButton

Private Sub menuItem_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    Debug.Print Ctrl.Caption 'you can add it to a collection or dictionary so that you could have the list available when needed
End Sub

2) In a regular module (Visual Basic Editor >> Insert >> Module)...

Option Explicit

Dim colShortCutMenuItems As Collection

Public Sub addShortCutMenuItems()

    Dim objMenuItem As clsMenuItem
    Dim objMenuBar As CommandBar
    Dim objControl As CommandBarButton
    Dim rngMenuList As Range
    Dim rngCell As Range
   
    Set colShortCutMenuItems = New Collection

    Set objMenuBar = Application.CommandBars("cell")
   
    With ThisWorkbook.Worksheets("Sheet1")
        Set rngMenuList = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
   
    For Each rngCell In rngMenuList
   
        Set objControl = objMenuBar.Controls.Add(Type:=msoControlButton, temporary:=True)
        With objControl
            .Caption = rngCell.Value
            .OnAction = "YourMacroName" 'change as required
        End With
       
        Set objMenuItem = New clsMenuItem
        Set objMenuItem.menuItem = objControl
       
        colShortCutMenuItems.Add objMenuItem
       
    Next rngCell
   
End Sub

Public Sub deleteShortCutMenuItems()

    Dim obj As Object
   
    On Error Resume Next
    For Each obj In colShortCutMenuItems
        Application.CommandBars("cell").Controls(obj.menuItem.Caption).Delete
    Next obj
    On Error GoTo 0
   
End Sub

Hope this helps!

 

[bobsan42]

you can pass the control caption to a general callback macro - then based on the caption the macro will choose what code will run next.
so you have a basic OnAction macro:

sub onActionMacro (ctlCaption as string)
select case ctlCaption 
Case "EAST" 
.....
case "MIDS"
...
Case else
....
end select
end sub

When you add controls to the context menu you do it like this:

        With .Controls.Add(1)  'msoControlButton = 1
            .Caption = "EAST"
            .OnAction = "'onActionMacro """ & .Caption & """'"
            .Style = msoButtonIconAndCaption
            .FaceId = 9139
            .BeginGroup = False
            .Tag = ""
        End With

The apostrophes are essential.
Or you can use the Tag property and go wild with the captions