VBA - Pivot Table Toggle Data field

biggsy3

New Member
Joined
Jan 27, 2014
Messages
42
Hi all,

I'm currently using this code to toggle pivot table row fields, there are buttons on my spreadsheet that I click to add/remove row fields in the pivot table.

This works great, however I was wandering if it is possible to adapt the code to toggle pivot table data field "values" by average.

I've played around with the code and i can get it to semi work.

I changed all the Rowfields to Datafields however the data just duplicates over and over and doesn't remove the previous field selection.


As you can probably see I am a complete novice to VBA, any help would be much appreciated. I have been scouring the net for answers with no joy so after a day of scratching my head I'm looking for someone to point me in the right direction.


Thank you in advance.



Sub Toggle_Row_Field()
'Add/Remove the field to the pivot table.
'The field is determined by the button text that calls the macro.


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


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

'Toggle field
'If visible then hide it
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
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hello Biggsy3,
I have same issue. And I have not been able to find any solutions to this issue.

Did you succed somehow?

Best regards,
Daniel
 
Upvote 0
Here is the code I run on large PTs to bulk change the default setting.
Public Sub PTAB___FieldsToSum()
' Cycles through all pivot data fields and sets to sum
' Created by Dr Moxie; Mods by Doug Johnson

'xlAverage -4106
'xlCount -4112
'xlCountNums -4113
'xlMax -4136
'xlMin -4139
'xlProduct -4149
'xlStDev -4155
'xlStDevP -4156
'xlSum -4157
'xlUnknown 1000
'xlVar -4164
'xlVarP

Dim SubTotalType As String
Dim pf As PivotField

'Add a user input box with default value xlSum
SubTotalType = InputBox("What type of summary do you want? Options are: xlSum, xlAverage, xlCount, xlMax, xlMin", "Summary Type", "xlSum")

With Selection.PivotTable
.ManualUpdate = True
For Each pf In .DataFields
With pf
'.Function = xlSum

'Cycle through predefined summary types
If SubTotalType = "xlMin" Then
.Function = xlMin
ElseIf SubTotalType = "xlCount" Then
.Function = xlCount
ElseIf SubTotalType = "xlAverage" Then
.Function = xlAverage
ElseIf SubTotalType = "xlMax" Then
.Function = xlMax
Else
.Function = xlSum
End If

.NumberFormat = "#,##0"
End With
Next pf
.ManualUpdate = False
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,222,312
Messages
6,165,276
Members
451,949
Latest member
bovacik

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top