How to get the PivotField i've clicked on?

Hi all. Here's my problem.

I've got a custom macro that i've assigned to the right click (Pivot Chart Popup Command Bar) menu in a Pivot Chart.

This is used when I right click on a Pivot Field and select the custom macro. This could be any field.
What I need is for the Name or Index of the field i've clicked on to be passed to the Macro, but i can't work out how?
I know it must be able to be done because all the built in functions know what i've clicked on (EG: Remove Field)...


Any ideas.

Thanks,
Julian

Try the following alternatives within the macro e.g. by assigning to a variable.....

ActiveCell.PivotField.Name

- returns the name of the field (as a string) that the current cell relates to.


ActiveCell.PivotField

- returns the pivot field object that the the current cell relates to.

You may have to build in error checking, unless your macro is only called when the activecell is within the pivot table range.

These simple statements also return the data field names (e.g. Sum of ... etc.)

Thanks Loz. Already got that one.

This is for an actual pivot chart. So there is no cells to speak of. Its just a right click on the field, I need to capture the field i've clicked on...
It must be able to be done?

Sorry - obviously didn't read your post closely.

The Chart_Select event can be used to identify what's been selected by the user. It's just a question of identifying the actual pivot field elements amongst all the other elements to return the field name you need.

I don't know what your existing macro does, or what it may need in terms of the pivot field name, but the following code should provide the required options . . .

Please Login or Register  to view this content.

The code sets up some public variables that your macro can pick up, and these should be self-explanatory. Using PivotObj(strFieldName) in your macro will give you the specific pivot field object to work on, but you will first need to check to ensure the Data object/field has not been selected (e.g. by using IF PivotObj is Nothing OR strFieldName = "Data" OR strFieldType = "Data" Then don't run the macro).

The last line Application.StatusBar = strFieldName & " : " & strFieldType can be taken out - it's included for testing purposes and shows what field and field type have been selected.

Sorry Loz, been out of action.

I tried but i'm getting an object required error on the Case 1 line?

I'm no VBA expert. I'm sure its something simple.

Thanks,
Julian

Not sure if you're still out there waiting for a response! I've not really been keeping in touch with forum activity.

I think the problem may relate to the use of "Chart1" in the code I gave. You will have to find out what the actual name of your chart is (which may be something other than "Chart1") and then change my example code accordingly. You could also try using ActiveChart instead of the Chart1 term.