In VSTO how can I load two context-senstitive ribbon tabs into an Excel project?

Posted on 2011-05-09
Last Modified: 2012-05-11
I have build an Excel project in VSTO using VB.Net and have included a new PivotTable context sensitive ribbon. Using the following function in ThisWorkbook.vb, it loads correctly and all works fine.

Protected Overrides Function CreateRibbonExtensibilityObject() As Microsoft.Office.Core.IRibbonExtensibility

However, I have now created another context-sensitive ribbon for PivotCharts. It seems the above 'Protected Overrides Function' can only be used to load 1 new ribbon (using Return New OdysseusToolsRibbon()) - it simply ignores the second line of code (Return New OdysseusChartRibbon()). I only need this second ribbon loaded when a PivotChart (or chart) is selected.

What event can I use to determine if a chart has been selected? ThisWorkbook_SheetSelectionChange is not triggered when you select a chart.

Also, assuming I can find when a chart is selected, how do I load the new ribbon? Is declaring it as a new ribbon enough? eg: Dim myChartRibbon As New OdysseusChartRibbon()
Question by:pennypearce
    LVL 83

    Expert Comment


    Author Comment

    Thanks. With this article I've managed to create a new class that does detect the selection when the chart is selected from within the code. However, it is not triggered when the code finishes running and I select the chart. I've probably messed something up trying to translate this from VBA to VSTO (I'm new to VSTO).

    What I've done:
    - created a new class called ChartHelper and put the code from the article into it.
    - In my existing class (called ExportData), I've referenced ChartHelper and assigned the chart that I build to it.
    - As mentioned when I select the chart from within the code, it triggers the Select event in ChartHelper.

    So, now I need to know how how to load a new context sensitive ribbon tab when a chart is selected. Once this is done once in the code, I assume the new ribbon tab will "stick" for all charts in this workbook once the code has finished running.

    The chart is actually a PivotChart, but there doesn't seem to be a distinction between a chart and PivotChart code in Excel 2010.

    Accepted Solution

    I have found the solution - it turned out to be trivial!
    I simply had to add the XML for the second ribbon (the chart context sensitive one) in with the XML for the first ribbon (the PivotTable context sensitive one). Because they are in the same ribbon class they load as one ribbon and all works beautifully.

    Author Closing Comment

    Not sure I should be doing this as I answered the question myself, but wanted to close it off.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Outlook Free & Paid Tools
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now