vba to add fields to pivot table, needs work

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
149
hi, i'm using the below code to add fields to my pivot table. it works great, but my problem is it only works for one pivot table on each page.

can someone help me edit this so that if there are multiple pivots on the same sheet?

if it helps, i'm just using the code this guy created in the "Pivot Table Fields Macro Buttons.xlsm" file here:
https://www.excelcampus.com/vba/macro-buttons-pivot-table-fields/



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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,810
Office Version
2007
Platform
Windows
Try this

Code:
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
  For Each pt In ActiveSheet.PivotTables
    '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
  Next
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,810
Office Version
2007
Platform
Windows
Did you try the updated macro of post # 2?
Did you have any problems or just want another help?
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
149
oops...didnt see that post. sorry. let me try now
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
149
i have 3 pivot tables on my worksheet. is there a way i can get identify, by name, which pivot tables are affected when i press each button?
or, is there a way i can indicate for the macro not to touch a pivot table named "V5"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,810
Office Version
2007
Platform
Windows
Try this

Code:
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, pf As PivotField
  Dim sField As String, shp As Shape
  Dim sName As String
  'Set variables
  For Each pt In ActiveSheet.PivotTables
    sName = pt.Name
    Select Case UCase(sName)
      Case "V5", "V6", "ETC" [COLOR=#0000ff]'not to touch these pivot table (capitalize names)[/COLOR]
      Case Else
        Set shp = ActiveSheet.Shapes(Application.Caller)
        sField = shp.TextFrame.Characters.text
        'Toggle field
        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 Select
  Next
End Sub
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
149
wow, beautiful...thanks so much Dante.

i noticed you code very cleanly, was wondering if you had any youtube videos to recommend?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,810
Office Version
2007
Platform
Windows
wow, beautiful...thanks so much Dante.

i noticed you code very cleanly, was wondering if you had any youtube videos to recommend?
I don't have any particular video.
When I have doubts I search on google ;)


Glad to help you, thanks for the feedback.
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
149
Hey Dante, I added another pivot table to the sheet, and when i use the buttons now, i'm getting this error:

"Unable to set the Orientation property of the PivotField class"

do you know why?



Try this

Code:
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
  For Each pt In ActiveSheet.PivotTables
    '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
  Next
End Sub
 

Forum statistics

Threads
1,077,993
Messages
5,337,597
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top