none
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?

    Saturday, April 14, 2012 9:19 AM

Answers

  • 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.


    Monday, April 16, 2012 2:14 PM

All replies

  • 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

    Monday, April 16, 2012 3:08 AM
  • 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.


    Monday, April 16, 2012 2:14 PM
  • 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

    Tuesday, April 17, 2012 5:16 AM