Simple(?) Pivot table VBA

[df9864]

I want to put some light code on a pivot table that expands / collapses detail.
The command to show / hide the detail works but I get an error on the IF statement:
"application or object defined error".
I suspect there's a problem with my syntax - any ideas?


If ActiveSheet.PivotTables("Mgr_Fcst_Plan_FTE1").PivotFields("Project Name").ShowDetail = False Then
ActiveSheet.PivotTables("Mgr_Fcst_Plan_FTE1").PivotFields("Project Name").ShowDetail = True
Else
ActiveSheet.PivotTables("Mgr_Fcst_Plan_FTE1").PivotFields("Project Name").ShowDetail = False

 

[df9864]

I could but don't want to crowd the worksheet (may have some extra buttons too).
There must be a way to assess the 'value' but can't figure it out.

ActiveSheet.PivotTables("Fcst_Invest_FTE1").PivotFields("Project Name").ShowDetail.Value

doesnt work either (ie. ".value")

 

[Comfy]

I'm not sure.

There is this:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pivotfield.showdetail.aspx

But I don't have 2010

 

[df9864]

hmm me neither - tho I do have Excel 2007 and it should work the same.
The code on the link is a little baffling. I've tried to declare / 'get' the value without success...
Surely there's a simple way to find out whether or not the pivot field is showing detail?

 

[Comfy]

If there is I don't know it and can't find any reference to it.

Sorry

 

[df9864]

Thanks for your help tho. .much appreciated.
Challenge to anyone else out there tho !!

FYI - not sure it makes any odds, but the pivot is in Excel 2007 but displaying as an old fashioned tabular pivot...