CODEWITHSUNDEEP
c#excelvstoadd-in
PuerNoctis
PuerNoctis

Reputation: 1384

Recognize selection of chart in Excel Add-In (with VSTO)

I have the rather small task of creating an Excel Add-In with VSTO in C# (it's a requirement), where I have to provide some information in a TaskPane as soon as a chart has been selected in any Workbook/Worksheet.

As I am totally new to VSTO or Office Extension in general, I don't seem to find any dedicated event or other possibility to recognize the moment the user selects an embedded chart in Excel.

I looked around and tried two things so far, but both don't seem to work as I intented them to:

  1. Listen to the Application.SheetSelectionChange event to intercept the selection of the chart. I soon found out that this event does not fire when a chart has been selected. The documentation states that this does not happen for ChartSheets, so I guess it is true for embedded charts, too.
  2. Traverse all the charts as soon as a worksheet has been activated and attach to a possible Activated event. But to my surprise, there is none (as far as I know).

After looking around the Interop API for a while without success (due to my inexperience with VSTO I may have not been looking in the right places though), I tried a very crude solution just to see if anything works for me.

I created a thread that polls the Application.ActiveChart property and, as soon as it is not null, I retrieve the information I need and display it in my TaskPane - otherwise I hide it.

This solution, of course, is the one I really don't want to use, especially under the cicrumstance that multithreading under COM can be very unforgiving very quickly.

How could I implement a solution that recognizes a user's selection of a chart in Excel as soon as it occurs? Any pointers or other clarifications about VSTO I might need to know?

Upvotes: 1

Views: 677

Answers (1)

penst
penst

Reputation: 23

There seems to be no dedicated event for that purpose. I used the CommandBar.OnUpdate event (.NET Reference) which is also fired, among other things, when a chart is selcted: 

using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    Excel.Application excelApp = Globals.ThisAddIn.Application;
    excelApp.CommandBars.OnUpdate += new Office._CommandBarsEvents_OnUpdateEventHandler(CommandBars_OnUpdate);

    //Other code to be executed at startup
}

private void CommandBars_OnUpdate()
{
        Excel.Application excelApp = Globals.ThisAddIn.Application;
        Excel.Chart activeChart = null;
        try
        {
            activeChart = excelApp.ActiveChart;
            if (activeChart != null)
            {
                //Code to be triggered
            }
        }
        catch
        {

        }
}

I got inspired by these two sources, where this approach is used to detect the selection of a shape:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/792c0159-863f-4db9-90be-25be94e875fb/any-workaround-sheetselectionchange-event-doesnt-occur-if-the-selection-is-on-a-chart?forum=vsto

https://code.msdn.microsoft.com/office/CSExcelNewEventForShapes-0e26b1f2

However, this event is fired quite a lot, so be careful with the code to be triggered by the event.

Hope that helps! Regards

Upvotes: 2

Related Questions