0

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.

TAKL
  • 339
  • 4
  • 11
  • Create a button in the `CommandBars("Cell")` object and assign your routine to the `.OnAction` property. https://msdn.microsoft.com/en-us/library/office/gg469862(v=office.14).aspx – Ambie Dec 01 '15 at 14:26
  • @Ambie, great suggestion! I hope you don't mind that I put together sample code for this in a revision to my answer, since I miss-understood the OP at first. If you want to post this as an answer I can remove mine. – u8it Dec 01 '15 at 15:56

2 Answers2

0

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
u8it
  • 3,956
  • 1
  • 20
  • 33
  • Actually, the function selects the comment, inserts my name (in bold), highlights it and puts the cursor at the end of the comment - ready to actually write a comment. Your suggestion only inserts a comment. There is a huge difference. – TAKL Dec 01 '15 at 15:13
  • @TAKL Sorry, I miss-understood the OP. Please see revision for what I understand it to be asking now. – u8it Dec 01 '15 at 15:52
  • You were on the right track from the beginning. However, I don't want to record a macro for every operation that I want to use. I want to USE that exact operation! The operation for "Insert comment" does something like this: - Insert comment - Display comment - Insert my name in bold and a Chr(10) - Place the cursor after my name, READY FOR WRITING A COMMENT. Another way of describing what I want to do is: - I want to CALL the "Insert comment" operation. I don't want to mimic it, I want to use that exact one. See my possible workaround (answer) below for more details. Hope you understand. – TAKL Dec 02 '15 at 08:45
0

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?

TAKL
  • 339
  • 4
  • 11