none
How to assign a method to a CommandbarButton in a Cell ContextMenu in an VSTO Application Addin for Excel 2010 (2007) using VB2010 RRS feed

  • Question

  • Hello,

    How can I assign a method to a commandbarButton in a Cell ContextMenu (Context Menu by right-click on a cell in a worksheet). The Cell Context Menu is defined in the ThisAddin_Startup method of the VSTO(4.0) application level addin for Excel 2010 (2007)

    I have a code snippet in C# translated to VB.NET.

    See this link for the C#  code : How to show a context menu on a range

    In C# the following code line assigns somekind of delegate to Button.Click

    button.Caption = "Refresh";
    button.BeginGroup = true;
    button.Tag = "MYRIGHTCLICKMENU";
    button.Click += new _CommandBarButtonEvents_ClickEventHandler(MyButton_Click);

    In VS2010 I write the code in VB.Net :

    Dim rcCellContextMenu As CommandBar = Globals.ThisAddIn.Application.CommandBars("Cell")
    Dim rcContextMenuButton1 As CommandBarButton = TryCast(rcCellContextMenu.FindControl(MsoControlType.msoControlButton, 1, "MYRIGHTCLICKMENU", , ), CommandBarButton)
        If (rcCellContextMenu Is Nothing) Then     
           Dim button As CommandBarButton = TryCast(rcCellContextMenu.Controls.Add(MsoControlType.msoControlButton, Id:=1, Before:=rcCellContextMenu.Controls.Count, Temporary:=True), CommandBarButton)
           If button IsNot Nothing Then
              button.Caption = "Refresh"
              button.BeginGroup = True
              button.Tag = "MYRIGHTCLICKMENU"
              button.Enabled = True
           End If
    End If

    In the C# code the assiging to button.click seems to work.

    The problem I encounter is that the methode .Click is not a member of the instance rcContextMenuButton1 of the interface CommandBarButton.

    The Object Inspector in VS2010 tells :

    -------------------------------------------------------------------------------------------------------------

    Event Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean)

        Member of Microsoft.Office.Core._CommandBarButtonEvents_Event

    Summary:
    Occurs when the user clicks a Microsoft.Office.Core.CommandBarButton object.

    ---------------------------------------------------------------------------------------------------------------

    So how can I use this Click event for the instance rcContextMenuButton1 of the interface CommandBarButton . And then how to assign a method to be executed on the click event. 

    This all have to be accomplished in the language of VB.NET

    I hope you can help me out

    Best Regards,

    Emile



    Tuesday, May 1, 2012 10:41 PM

Answers

  • Hello T.X.

    In Excel 2010, the button with Caption 'Refresh' appears succesfull in the Cell Contextmenu by right clicking on a excel cell. (By the way, I hat to remove the lines of code which declares rcContextMenuButton1 and the if statement who checks if the findcontrol method found the control items in the CellContextmenu).

    I followed your recommendations and noticed the appearens of the Click method for the withevents button after the 'Handles' keyword.

    But the Sub method who handles the button.click is not invoked.

    When I examine the button more closely I recognize that the declared 'Private Withevents button as CommandbarButton in the body of ThisAddin is not the same button as the declared 'Dim button As CommandBarButton' in the method ThisAddin_Startup.

    So I removed also the local declaration of button in the method ThisAddin_Startup and only kept the code who assigns the casted control.add to de variable button 

    Furthermore I implemented arguments to the method Private Sub click who fullfill the signature of a CommandBarButton click event.

    With these changes the Private Sub click method is succesfull invoked when clicking the button on the Cell ContextMenu

    The code now looks like this:

    Imports Microsoft.Office.Core
    
    Public Class ThisAddIn
    
        Private WithEvents button As CommandBarButton
    
        Private Sub ThisAddIn_Startup() Handles Me.Startup
            Dim rcCellContextMenu As CommandBar = Globals.ThisAddIn.Application.CommandBars("Cell")
            button = TryCast(rcCellContextMenu.Controls.Add(MsoControlType.msoControlButton, Id:=1, Before:=rcCellContextMenu.Controls.Count, Temporary:=True), CommandBarButton)
            If button IsNot Nothing Then
                button.Caption = "Refresh"
                button.BeginGroup = True
                button.Tag = "MYRIGHTCLICKMENU"
                button.Enabled = True
            End If
        End Sub
    
        Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
    
        End Sub
    
        Friend Sub click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles button.Click
            System.Windows.Forms.MessageBox.Show("Button was Clicked", "MyVSTOAddin")
        End Sub
    
    End Class

    T. X. I thank you for your advise.

    Beste regards,

    Emile


    Thursday, May 3, 2012 9:45 AM

All replies

  • HI Emile,

    The following snippet will works in theory.

    Imports Microsoft.Office.Core
    
    Public Class ThisAddIn
        Private WithEvents button As CommandBarButton
    
        Private Sub ThisAddIn_Startup() Handles Me.Startup
            Dim rcCellContextMenu As CommandBar = Globals.ThisAddIn.Application.CommandBars("Cell")
            Dim rcContextMenuButton1 As CommandBarButton = TryCast(rcCellContextMenu.FindControl(MsoControlType.msoControlButton, 1, "MYRIGHTCLICKMENU", , ), CommandBarButton)
            If (rcCellContextMenu Is Nothing) Then
                Dim button As CommandBarButton = TryCast(rcCellContextMenu.Controls.Add(MsoControlType.msoControlButton, Id:=1, Before:=rcCellContextMenu.Controls.Count, Temporary:=True), CommandBarButton)
                If button IsNot Nothing Then
                    button.Caption = "Refresh"
                    button.BeginGroup = True
                    button.Tag = "MYRIGHTCLICKMENU"
                    button.Enabled = True
                End If
            End If
        End Sub
    
        Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
    
        End Sub
    
        Private Sub click() Handles button.Click
            MsgBox("This is a test")
        End Sub
    
    End Class

    Best Regards,

    T.X.

    Thursday, May 3, 2012 5:15 AM
  • Hello T.X.

    In Excel 2010, the button with Caption 'Refresh' appears succesfull in the Cell Contextmenu by right clicking on a excel cell. (By the way, I hat to remove the lines of code which declares rcContextMenuButton1 and the if statement who checks if the findcontrol method found the control items in the CellContextmenu).

    I followed your recommendations and noticed the appearens of the Click method for the withevents button after the 'Handles' keyword.

    But the Sub method who handles the button.click is not invoked.

    When I examine the button more closely I recognize that the declared 'Private Withevents button as CommandbarButton in the body of ThisAddin is not the same button as the declared 'Dim button As CommandBarButton' in the method ThisAddin_Startup.

    So I removed also the local declaration of button in the method ThisAddin_Startup and only kept the code who assigns the casted control.add to de variable button 

    Furthermore I implemented arguments to the method Private Sub click who fullfill the signature of a CommandBarButton click event.

    With these changes the Private Sub click method is succesfull invoked when clicking the button on the Cell ContextMenu

    The code now looks like this:

    Imports Microsoft.Office.Core
    
    Public Class ThisAddIn
    
        Private WithEvents button As CommandBarButton
    
        Private Sub ThisAddIn_Startup() Handles Me.Startup
            Dim rcCellContextMenu As CommandBar = Globals.ThisAddIn.Application.CommandBars("Cell")
            button = TryCast(rcCellContextMenu.Controls.Add(MsoControlType.msoControlButton, Id:=1, Before:=rcCellContextMenu.Controls.Count, Temporary:=True), CommandBarButton)
            If button IsNot Nothing Then
                button.Caption = "Refresh"
                button.BeginGroup = True
                button.Tag = "MYRIGHTCLICKMENU"
                button.Enabled = True
            End If
        End Sub
    
        Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
    
        End Sub
    
        Friend Sub click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles button.Click
            System.Windows.Forms.MessageBox.Show("Button was Clicked", "MyVSTOAddin")
        End Sub
    
    End Class

    T. X. I thank you for your advise.

    Beste regards,

    Emile


    Thursday, May 3, 2012 9:45 AM