8. VSTO Bug Tracker : Using a Ribbon

To get started, from the Project menu choose Add New Item. In the Add New Item dialog box, click Ribbon (Visual Designer). Then click the Add button. A new Ribbon is created and the Ribbon Designer is displayed.

Next, click on the Group control that is created for you and change the Label in the Properties window to “Bugs” as well. Drag and drop a Button control from the Toolbox onto the Group control.  Use the Properties window to set the Label for the button to “Add Bug Data”.  Also, set the ControlSize to RibbonControlSizeLarge and set an Image for the button if you like.  Double click on the Button control to add an event handler.  The event handler will invoke the PopulateSpreadsheet method.  You can now remove the SheetBeforeRightClick handler that we added earlier if you like.

     Private Sub AddBugData_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles AddBugData.Click
        Globals.ThisAddIn.PopulateSpreadsheet(True)
    End Sub

Then, drag and drop a Gallery control from the Toolbox onto the Group control. Use the Properties window to set the Label for the Gallery control to “Bug Charts”. Also, set the ControlSize to RibbonControlSizeLarge and set an Image for the Gallery control if you like.

With the Gallery control configured the way we want, let’s write some code behind it. Click on the Gallery control again to select it. Then click on the lightning bolt icon button in the Properties window to show the events for the Gallery control. Three events are displayed. Click is raised when an item in our Items collection is clicked. And ItemsLoading is raised when the user drops down the Gallery control before its contents are displayed—this is the event that can be handled at runtime to modify the Items and Buttons lists before the Gallery control is displayed to the user.

We want to add handlers for ItemsLoading and Click. First, let’s write the handler for the ItemsLoading event. Double click on the ItemsLoading event in the Properties window and Visual Studio generates an event handler in the code behind the Ribbon and displays the event handler. Add the code below.  What does this code do?  It will dynamically populate the Gallery control with a RibbonDropDownItem for each chart in the workbook.  It sets the RibbonDropDownItem to the image of the chart.  It also tags each RibbonDropDownItem with an identifier that the Click event will use.

     Private Sub Gallery1_ItemsLoading(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Gallery1.ItemsLoading
        Gallery1.Items.Clear()

        Dim c As Object
        Dim s As Excel.Worksheet
        Dim tag As Integer

        Dim tempFileName As String = System.IO.Path.GetTempFileName()

        For Each s In Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets
            Dim i As Integer
            For i = 1 To s.ChartObjects.Count
                Dim d As New RibbonDropDownItem
                c = s.ChartObjects(i)

                c.Chart.Export(tempFileName, "BMP")
                Dim bmpTemp As New System.Drawing.Bitmap(tempFileName)
                Dim bmpUnlinked As New System.Drawing.Bitmap(bmpTemp)
                bmpTemp.Dispose()
                d.Image = bmpUnlinked

                d.Tag = tag
                Gallery1.Items.Add(d)
                tag = tag + 1
            Next
        Next

        System.IO.File.Delete(tempFileName)
    End Sub

Now click on the Gallery control again to select it, then double click on the Click event in the Properties window and Visual Studio generates an event handler in the code behind the Ribbon and displays the event handler.   In the Click event we will activate the chart corresponding to the RibbonDropDownItem.

     Private Sub Gallery1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Gallery1.Click
        Dim tagFound As Integer = Gallery1.SelectedItem.Tag
        Dim s As Excel.Worksheet
        Dim tag As Integer

        For Each s In Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets
            Dim i As Integer
            For i = 1 To s.ChartObjects.Count
                If tagFound = tag Then
                    s.ChartObjects(i).Activate()
                    Exit Sub
                End If
                tag = tag + 1
            Next
        Next
    End Sub

When we run the add-in, we get this result when you drop down the gallery control for a document with two charts in it.  The gallery control updates to display images of the current charts in the workbook.  If you click one of the drop down items, it activates the corresponding chart in the workbook.

That’s all folks!  Hoped you enjoyed this example of using VSTO.