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

Hi Kannan,

Thanks for posting in the MSDN forum.

Do you use Office 2007 or Office 2010?

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 2

I use Office 2010. In fact I have found the solution of this problem. The same is given below

Public Class CC

	Private WithEvents A1 As Office.CommandBarButton
	Private WithEvents A2 As Office.CommandBarButton
	Private WithEvents A3 As Office.CommandBarButton

    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

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

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

        With NewControl.Controls.Add(Microsoft.Office.Core.MsoControlType.msoControlButton)
            .Caption = "A3"
            .FaceId = 1155
        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
	
    Private Sub A1_Click(ByVal Ctrl As Office.CommandBarButton, ByRef CancelDefault As Boolean) Handles A1.Click
        A1()
    End Sub
	
    Private Sub A2_Click(ByVal Ctrl As Office.CommandBarButton, ByRef CancelDefault As Boolean) Handles A2.Click
        A2()
    End Sub
	
    Private Sub A3_Click(ByVal Ctrl As Office.CommandBarButton, ByRef CancelDefault As Boolean) Handles A3.Click
        A3()
    End Sub

End Class

Now that I have solved this problem, I have another question.

I want to perform Application.Onkey event from visual studio. If anyone knows how to do it then please post it here.

Answer 3

Hi Kannan,

I'm glad to hear you have solved it by yourself. I would recommend you post your new issue in a new thread. So that communitry members can retrieve it easier and you are able to receive better support.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us