This is a reply to forum and blog topics "Regarding click event of charts", which can be seen here:

excelexperts.com/regarding-click-event-charts

excelexperts.com/regarding-click-event-charts-0

Hello, Shivam,

Perhaps there is a better way to accomplish this task, but I'm still not quite sure what you are trying to achieve. I hope what I have done to meet your requirements, and if not, I will change it after you share more details.

And now some explanation...

I create two variants of usage of chart click event and attach files both for Excel 2003 and Excel 2007 versions. You are not mentioned which version you use. In both variants I use the same logic: two pivot tables, two pivot charts and report filter for one of them, which filter is managed from chart click event. The difference between them is how the source code is organized.

Variant-1 - I create a class clsChart where I declare a click event without a code, because in that way it can be used many times. And in an another class (clsChartEvent) there is an example how this happen - I declare two objects of clsChart type, one for each chart in the sheet, next I use the same click event of clsChart object and write a different code for each chart. First click event subroutine is for manage the report filter. Second one is just for example how can use one event for many charts. However, you may notice that click event not work on first click. This is because on first click you select all series points for a desired series, and on second click you actually select the desired point. If this is not you need you may workaround it like in a clsChart use a MouseUp or MouseDown event instead of Select event, and use a GetChartElement method to get the ElementID, Arg1 and Arg2 arguments with which you can identify the element on which you actually click. In that way will be need only one click to get the desired element, but thus you lose the possibility to work with all series points if for some reason you need this.

Variant-2 - there is nothing in particular than in Variant-1. Here you not need separate class for declare class events, because each chart have own source code module, where you can do that. The source code is the same as in Variant-1. The difference is where the code is placed.

If I miss something, please tell me.

And once again - please excuse me for my English. I hope it is enough to be understood.

Best regards.