Macro Buttons to Add DataFields to Pivot Tables

[Damkilde1986]

Good day,

I know this topic has been mentioned and discribed in several threads, but so far I have not been able to locate any with a succesfull result.

This below works quite well on rows, but changing xlRowField to xlDataField do not solve the problem. The macro just keeps adding the same field to the pivot table, and not as wanted toggle the data field on/off.

Hope some of you have a solution. It seems that several are looking for a solution.

Thanks in advance!

/Daniel


Sub Toggle_Row_Field()

Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String
Dim shp As Shape

Set pt = ActiveSheet.PivotTables(1)
Set shp = ActiveSheet.Shapes(Application.Caller)
sField = shp.TextFrame.Characters.Text

If pt.PivotFields(sField).Orientation = xlRowField Then
pt.PivotFields(sField).Orientation = xlHidden
shp.Fill.ForeColor.Brightness = 0.5
Else 'Add to Rows area
pt.PivotFields(sField).Orientation = xlRowField
shp.Fill.ForeColor.Brightness = 0
End If

End Sub