Add a function from the default Right-Click Context Menu into a custom context menu?

Question

Is there any way to access the code for all the built-in Cell Context Menu functions?

I'm specifically looking to run the "AddComment" function from the RightClick Context Menu - but I've built a custom context menu.

What it does is not only add a comment. It opens the comment and adds my name and selects it. It's nice and I'd like to apply that function in a custom context menu. So I either need to get a hold on the code for that function, or 'call' the function itself from my custom context menu.

EDIT: I think I need to be more specific:

• The standard operation that's called "Insert comment" is located in the cell right-click context menu.
• I want to copy that function into my own (custom) right-click context menu.
• Is there any place one can see the actual code for all the built-in functions in excel? I'm talking about these right-click functions, for example.

So, I don't want to hijack a context menu. I want to use it! But I've created a custom right-click context menu, where I want to insert a function from the original right-click context menu.

Answer 1

You should be able to do pretty much everything from the normal cell context menu using VBA. If you use record macro, then you can click through the menu and then look at the code to see what was recorded. For instance, macro recording for inserting a comment produces this code....

Range("A1").Select
Range("A1").AddComment
Range("A1").Comment.Visible = False
Range("A1").Comment.Text Text:="Your Name:" & Chr(10) & ""

Edit
I think I miss understood the OP at first as asking how to get the corresponding VBA code for default context-menu operations. I think what's actually being asked is how to "hijack" the built-in context menu operation itself. As Ambie commented, this can be done as shown below.

Sub HijackContextMenuInsertComment()
    Dim ContextMenu As CommandBar
    Set ContextMenu = Application.CommandBars("Cell")
    ContextMenu.Controls.Item(13).OnAction = "Test"
End Sub
Sub Test()
    MsgBox "I've been hijacked"
End Sub

Answer 2

Possible workaround:

Step by step:

• Use default context menu
• Remove all items except "Insert comment"
• Add custom functions to the context menu
• Now I should have all my own functions AND the "Insert comment" function.

To make this work, I simply added this code:

Dim Cnt As CommandBarControl
With Application.CommandBars("Cell")
    For Each Cnt In .Controls
        If InStr(1, Cnt.Caption, "Infoga ko") = 0 Then Cnt.Delete
    Next Cnt
End with

Please note that the caption differs in every language. I've got it in swedish, so in english it might work with "Insert c" instead of "Infoga ko".

In order to get the correct caption, you can loop all controls like this:

For Each Cnt In .Controls
    Debug.Print Cnt.Caption
Next Cnt

After running this code, I'll have only the "Insert comment" function left and then I can start adding my own controls!

UPDATE: Nope, this is not working. The function won't run. Nothing happens when clicking the "Insert comment" function. Weird?