How to perform an action on clicking a custom context menu created in excel using Excel Add-In created with visual studio 2010

Question

I am creating an Excel Add-In using Visual Studio 2010. My intention was to add a context menu to a cell and perform some action on the selected cell or cells. Here is the code I have got as of now

    Public Class CC

    Private Sub ThisAddIn_Startup() Handles Me.Startup
        AddMenu()
    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
        DeleteMenu()
    End Sub

    'AddMenu add context menu to excel
    Sub AddMenu()
        On Error Resume Next
        Dim Bar As Microsoft.Office.Core.CommandBar
        Dim NewControl As Microsoft.Office.Core.CommandBarControl
        Application.CommandBars("Cell").Controls("A").Delete()
        Bar = Application.CommandBars("Cell")
        NewControl = Bar.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlPopup, Id:=1, Temporary:=True)

        With NewControl
            .Caption = "A"
            .BeginGroup = True
            .TooltipText = "Change case of selected cells."
        End With

        With NewControl.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlButton)
            .Caption = "A1"
            .FaceId = 1144
            .OnAction = "A1"
        End With

        With NewControl.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlButton)
            .Caption = "A2"
            .FaceId = 1145
            .OnAction = "A2"
        End With

        With NewControl.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlButton)
            .Caption = "A3"
            .FaceId = 1155
            .OnAction = "A3"
        End With

    End Sub

    'DeleteMenu deletes the context meny added to excel
    Sub DeleteMenu()
        On Error Resume Next
        Application.CommandBars("Cell").Controls("A").Delete()
    End Sub

    Sub A1()
        MsgBox "A1"
    End Sub

    Sub A2()
        MsgBox "A2"
    End Sub

    Sub A3()
        MsgBox "A3"
    End Sub

End Class

When I install this Add-In the context menu appears in excel, but when I click on the menu buttons I get an error saying that the macro is not available in the workbook. Can anyone please tell me how to make it work?

Answer 1

Your methods A1, A2 and A3 will not automatically be registered as macros with Excel. As a result setting their names into the OnAction strings of the buttons have no effect - Excel doesn't know about a macro called "A1". So in this sense the VSTO add-in does not behave like the code in VBA would at all.

There is another approach though: For the CommandBar Buttons you can add event handlers - you'd use the WithEvents keyword and then handle the Click event of the buttons. Some examples that might get you started are here: http://msdn.microsoft.com/en-us/library/aa189726(v=office.10).aspx

Using Excel-DNA (an open source .NET / Excel integration library that I develop) the methods and user-defined functions in your .NET code are registered with Excel through the C API. As a result the behaviour is closer to that of VBA, and your code with the OnAction="..." strings would work too.