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

 

[Comfy]

Do you have an

End if

??

 

[df9864]

yep - i had an End IF - just didnt paste in that line...

 

[Comfy]

yep - i had an End IF - just didnt paste in that line...

Just checking

 

[df9864]

I'm not that good but then again I'm not that bad !

 

[Hermanito]

Maybe your activesheet is not what you expect when you run the code? Then it's possible it can't find said pivottable, hence the error...

Try to replace ActiveSheet with the real sheet codename...

 

[Comfy]

The error is down to ActiveSheet.PivotTables("Mgr_Fcst_Plan_FTE1").PivotFields("Project Name").ShowDetail

You can set this to True/False but when checking the value VB returns

<Application-defined or object defined error>

 

[df9864]

Comfy - can you expand on that?
Cheers

 

[Comfy]

I don't know how to word it.

You can set .Showdetail to True or False

but you can't evaluate .Showdetail to find out currently if it True or False.

Trying to see if there is another way

 

[Comfy]

Could you have two command buttons?

One for:

ActiveSheet.PivotTables("Mgr_Fcst_Plan_FTE1").PivotFields("Project Name").ShowDetail = True

The other for:

ActiveSheet.PivotTables("Mgr_Fcst_Plan_FTE1").PivotFields("Project Name").ShowDetail = False