CODEWITHSUNDEEP
vb.netvisual-studio-2010excelexcel-addinsvba
Kannan Suresh
Kannan Suresh

Reputation: 4580

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

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?

Upvotes: 3

Views: 4482

Answers (3)

callee
callee

Reputation: 1

This is a bear of a problem. Very little info anywhere on creating excel popups in vb.net. This is my version of creating semi dynamic menus. The menu items come from dictionaries in this case but could come from anywhere. Created this class and loaded it on workbook startup event.

Public Class Popups Private mCmdBarPopFH As Microsoft.Office.Core.CommandBarPopup Private mCmdBarPopPH As Microsoft.Office.Core.CommandBarPopup Private mCmdBarPopRH As Microsoft.Office.Core.CommandBarPopup Private WithEvents tagFH1 As Microsoft.Office.Core.CommandBarButton Private WithEvents tagFH2 As Microsoft.Office.Core.CommandBarButton Private WithEvents tagFH3 As Microsoft.Office.Core.CommandBarButton Private WithEvents tagPH1 As Microsoft.Office.Core.CommandBarButton Private WithEvents tagPH2 As Microsoft.Office.Core.CommandBarButton Private WithEvents tagPH3 As Microsoft.Office.Core.CommandBarButton Private WithEvents tagRH1 As Microsoft.Office.Core.CommandBarButton Private WithEvents tagRH2 As Microsoft.Office.Core.CommandBarButton Private WithEvents tagRH3 As Microsoft.Office.Core.CommandBarButton Private WithEvents tag1st As Microsoft.Office.Core.CommandBarButton Private WithEvents tag2nd As Microsoft.Office.Core.CommandBarButton Private WithEvents tagClr As Microsoft.Office.Core.CommandBarButton Private mFHDefDict As New Dictionary(Of String, HeaderDef) Private mPHDefDict As New Dictionary(Of String, HeaderDef) Private mRHDefDict As New Dictionary(Of String, HeaderDef)

Private mPHSheet As Excel.Worksheet  'temp until sheet management
Private mRHSheet As Excel.Worksheet
Private mFHSheet As Excel.Worksheet

'************************************************************************************
'Add popup menu for marking sample file.
'************************************************************************************
Public Sub TagsMenuAdd()
    Dim oHeaderDefs As New HeaderDefs
    Dim oCmdBar As Microsoft.Office.Core.CommandBar

    mFHSheet = CType(Globals.ThisWorkbook.Application.Sheets("File Headers"), Excel.Worksheet)
    mPHSheet = CType(Globals.ThisWorkbook.Application.Sheets("Plate Headers"), Excel.Worksheet)
    mRHSheet = CType(Globals.ThisWorkbook.Application.Sheets("Read Headers"), Excel.Worksheet)
    mFHDefDict = oHeaderDefs.DefDictLoad(mFHSheet)  'temp until sheet management
    mPHDefDict = oHeaderDefs.DefDictLoad(mPHSheet)
    mRHDefDict = oHeaderDefs.DefDictLoad(mRHSheet)

    oCmdBar = Globals.ThisWorkbook.Application.CommandBars.Add(Name:="Fil_CellMarking", Position:=Microsoft.Office.Core.MsoBarPosition.msoBarPopup, Temporary:=True)
    With oCmdBar
        tag1st = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
        tag1st.Caption = "Mark 1st Well of 1st data set"
        tag1st.Tag = "1st"
        tag2nd = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
        tag2nd.Caption = "Mark 1st Well of 2nd data set"
        tag2nd.Tag = "2nd"
        mCmdBarPopFH = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlPopup), Microsoft.Office.Core.CommandBarPopup)
        With mCmdBarPopFH
            .Caption = "Mark File Headers"
            .Enabled = True
        End With
        mCmdBarPopPH = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlPopup), Microsoft.Office.Core.CommandBarPopup)
        With mCmdBarPopPH
            .Caption = "Mark Plate Headers"
            .Enabled = True
        End With
        mCmdBarPopRH = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlPopup), Microsoft.Office.Core.CommandBarPopup)
        With mCmdBarPopRH
            .Caption = "Mark Read Headers"
            .Enabled = True
        End With
        tagClr = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
        tagClr.Caption = "Clear All Markings"
        tagClr.Tag = "clr"
    End With

    TagsMenuItemsFH(mFHDefDict)
    TagsMenuItemsPH(mPHDefDict)
    TagsMenuItemsRH(mRHDefDict)
End Sub
'************************************************************************************
'Add popup menu items for marking sample file.
'************************************************************************************
Public Sub TagsMenuItemsFH(DefDict As Dictionary(Of String, HeaderDef))
    Dim iButtons As Integer
    iButtons = 1
    For Each sKey As String In DefDict.Keys
        Select Case iButtons
            Case 1
                With mCmdBarPopFH
                    tagFH1 = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
                    tagFH1.Caption = DefDict(sKey).HeaderName
                    tagFH1.Tag = "FH1"
                End With
            Case 2
                With mCmdBarPopFH
                    tagFH2 = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
                    tagFH2.Caption = DefDict(sKey).HeaderName
                    tagFH2.Tag = "FH2"
                End With
            Case 3
                With mCmdBarPopFH
                    tagFH3 = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
                    tagFH3.Caption = DefDict(sKey).HeaderName
                    tagFH3.Tag = "FH3"
                End With
        End Select
        iButtons = iButtons + 1
    Next
End Sub
Public Sub TagsMenuItemsPH(DefDict As Dictionary(Of String, HeaderDef))
    Dim iButtons As Integer
    iButtons = 1
    For Each sKey As String In DefDict.Keys
        With mCmdBarPopPH
        Select iButtons
                Case 1
                    tagPH1 = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
                    tagPH1.Caption = DefDict(sKey).HeaderName
                    tagPH1.Tag = "PH1"
                Case 2
                    tagPH2 = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
                    tagPH2.Caption = DefDict(sKey).HeaderName
                    tagPH2.Tag = "PH2"
                Case 3
                    tagPH3 = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
                    tagPH3.Caption = DefDict(sKey).HeaderName
                    tagPH3.Tag = "PH3"
            End Select
        End With
        iButtons = iButtons + 1
    Next
End Sub
Public Sub TagsMenuItemsRH(DefDict As Dictionary(Of String, HeaderDef))
    Dim iButtons As Integer
    iButtons = 1
    For Each sKey As String In DefDict.Keys
        With mCmdBarPopRH
            Select Case iButtons
                Case 1
                    tagRH1 = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
                    tagRH1.Caption = DefDict(sKey).HeaderName
                    tagRH1.Tag = "RH1"
                Case 2
                    tagRH2 = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
                    tagRH2.Caption = DefDict(sKey).HeaderName
                    tagRH2.Tag = "RH2"
                Case 3
                    tagRH3 = CType(.Controls.Add(Type:=Microsoft.Office.Core.MsoControlType.msoControlButton), Microsoft.Office.Core.CommandBarButton)
                    tagRH3.Caption = DefDict(sKey).HeaderName
                    tagRH3.Tag = "RH3"
            End Select
        End With
        iButtons = iButtons + 1
    Next
End Sub
Private Sub Button_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles tag1st.Click, tag2nd.Click, tagClr.Click
    Select Case Ctrl.Tag
        Case "1st"
            MsgBox("1st")
        Case "2nd"
            MsgBox("2nd")
        Case "clr"
            MsgBox("clr")
    End Select
End Sub
Private Sub Header_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) Handles tagFH1.Click, tagFH2.Click, tagFH3.Click, tagPH1.Click, tagPH2.Click, tagPH3.Click, tagRH1.Click, tagRH2.Click, tagRH3.Click
    Select Case Ctrl.Tag
        Case "FH1"
            MsgBox("FH1")
        Case "FH2"
            MsgBox("FH2")
        Case "FH3"
            MsgBox("FH3")
        Case "PH1"
            MsgBox("PH1")
        Case "PH2"
            MsgBox("PH2")
        Case "PH3"
            MsgBox("PH3")
        Case "RH1"
            MsgBox("RH1")
        Case "RH2"
            MsgBox("RH2")
        Case "RH3"
            MsgBox("RH3")
    End Select
End Sub

End Class

Upvotes: 0

Kannan Suresh
Kannan Suresh

Reputation: 4580

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

This is the solution that I found for the above problem

Upvotes: 0

Govert
Govert

Reputation: 16907

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.

Upvotes: 6

Related Questions