add item to right-click menu in Excel chart

Question

How to add item to right-click menu in Excel chart using VBA? Excel is 2007. Chart is standalone sheet.

Answer 1

You want to use the CommandBars collection, which is used for the context menus in addition to the toolbars. There are at least three context menus - cell (right-clicking on any cell), row and column (right clicking on the A, B, C or 1, 2, 3 headers). Very basic code for adding a new element to the row context menu is below.

See also the MSDN documentation for CommandBars, and there's an old Office 2000 list of commandbar elements.

Sub AddToContextMenu()
    Dim element As CommandBarButton
    Set element = CommandBars("row").Controls.Add
    element.Caption = "Menu Item Name"
    element.OnAction = "VBAToRunOnRow"
End Sub

Answer 2

I know you're not asking for a C# solution but I thought it'd be useful to post one since I just worked it out from Alistair's answer:

public void AddContextMenuItem(Microsoft.Office.Interop.Excel.Application application, string label)
{
    var button = (CommandBarButton)application.CommandBars["cell"].Controls.Add();
    button.Caption = label;
    button.Click += button_Click;
}

void button_Click(CommandBarButton Ctrl, ref bool CancelDefault)
{
    //Button click
}