How to perform an action on clicking a custom context menu created in excel using Excel Add-In created with visual studio 2010
excel, excel-addins, vb.net, vba, visual-studio-2010
Solution
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.
Problem
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?