How to know which column the user has selected (clicked on)?
I have created a pivot table chart with 2 dimensions and 5 expressions and I would like to know how to catch the event click when a user selects one of the monthly columns.
The user will select any cell to narrow the data shown. I need to know which column was selected (it is not showing in the current selections box) to run a macro depending of which month was selected, 3 columns show a distinct count for each month, I focus in the middle ones; see the image showing just the 5 expressions.
![Pivot.JPG](/legacyfs/online/17128_Pivot.JPG)
Expressions are.-
“Monthly Target” Definition: ( Column(2) + Column(3) + Column(4) ) / 3
Label for column2 ='Past 2 Months ' & varPMonth & ' ‘ & varCYear
Label for column3='Last Month ' & & varLMonth & ' ‘ & varCYear
Label for column4='Current Month ' & & varCMonth & ' ‘ & varCYear
“Daily Trend - Past 3 months” definition: ( Column(2) + Column(3) + Column(4) )
As you can see the column names are calculated and not coming from a field so I cannot use field event trigger there.
The content for every monthly cell is calculated as well.-
For past 2 months: if(Row='1' count({} Distinct Number),
For last month: if(Row='1' count({} Distinct Number)
For current month: if(Row='1' count({} Distinct Number)
I’m using this variables.-
Let varCYear = Year(Today());
Let varPMonth = Month(Today()-65);
Let varLMonth = Month(Today()-35);
Let varCMonth = Month(Today());
So basically I want to catch the month that user selected in (current, last or past columns).
When a row is selected in that chart I can trigger a macro (using VBScript) to catch some fields strings like “status” and so on (horizontally but not vertically for field “month”)
Any suggestions?
Thank you.