Code for Pivot Charts

T

tjgrassi

Board Regular
Joined
Jun 10, 2014
Messages
51
I have a code for a user form that works well for the most part. It is a long code. I was wondering if there is any way to turn off a current Pivot Table Field that is selected, the only way I could figure out how to do it was turn each field off at the begining of the macro, this is the code I currently have to start my macro. I could run in to problems if someone where to try to change the pivot field manually and excel puts the new category in the row lable instead of values or if it was in values and not as a sum but as "count." I would need a code 3 times this long to cover all of the basis.

On Error Resume Next
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Haz Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Non-Haz Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Ink Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Water Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Gas Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Electric Intensity") _
.Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Haz (lbs)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Non-Haz (Lbs)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Ink (Gallons)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Water (Gallons)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Gas (Therms)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Electric (kWh)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Output (Packages)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Output Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Electricity Percent Change" _
).Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Gas Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Water Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Ink Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Non-Haz Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Haz Percent Change") _
.Orientation = xlHidden​
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
This will do what you asked:

Code: 
Sub TurnOffAllPTFields()
    
    Dim lX As Long
    
    With ActiveSheet.PivotTables(1)
        For lX = 1 To .PivotFields.Count
            .PivotFields(lX).Orientation = xlHidden
        Next
    End With

End Sub

If you have more that 1 PivotTable on a worksheet, change ActiveSheet.PivotTables(1)
back to ActiveSheet.PivotTables("PivotTable7")
 
Upvote 0
What version of Excel are you using? That code worked for me with Excel 2010

Try this:
Code: 
Sub TurnOffAllPTFields()
    
    Dim lX As Long
    
    With ActiveSheet.PivotTables(1)
        Debug.Print "PivotFields.Count = " & .PivotFields.count  'debugging
        .PivotFields(1).Orientation = xlRowField 'First field will be visible
        For lX = 2 To .PivotFields.count
            .PivotFields(lX).Orientation = xlHidden
        Next
    End With

End Sub
 
Upvote 0
I'm running 2010, this code didnt work either. It turned off 2 of my fields and didnt turn off what i needed. I think the only wayto turn off what i need is to keep it the way i have it. Thanks though
 
Upvote 0
Both subs worked as I expected on the simple PT that I created for testing. A more complex PT apparently results in a situation that neither sub could fix. If you want to continue looking for a better way could you post your headers and 5 lines of data and explain which columns are assigned to each of Report Filter, Column, Row and Values. And if any of the values are other than Sum.
 
Upvote 0
I think I got it to work, however when I ran my user form and selected the cateogry on the chart I wanted to see It didn't reconigze the line of code that turns back on. heres the start of my code. I used your code to turn off all field options and then i turn on all years to look at all the data from those years, and then i turn on the fields that i need on there everytime and then I start with the select case to for when a user would select an option from a combo box on the user form. The code messed up on the red code. I am only using sums, but I would like to turn off all fields just in case a user messes with the field list in the pivot chart. Thanks

Dim lX As Long

With ActiveSheet.PivotTables(1)
Debug.Print "PivotFields.Count = " & .PivotFields.Count 'debugging
' .PivotFields(1).Orientation = xlRowField 'First field will be visible
For lX = 2 To .PivotFields.Count
.PivotFields(lX).Orientation = xlHidden
Next
End With

ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Year")
.PivotItems("2010 Data").Visible = True
.PivotItems("2011 Data").Visible = True
.PivotItems("2012 Data").Visible = True
.PivotItems("2013 Data").Visible = True
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Site")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Year")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Year")
.Orientation = xlColumnField
.Position = 1
End With
Application.ScreenUpdating = False


Select Case ComboBox1
Case "Water":

ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("Water (Gallons)"), "Sum of Water (Gallons)", xlSum
 ActiveSheet.PivotTables("PivotTable7").PivotFields("Site").AutoSort _
xlDescending, "Sum of Water (Gallons)", ActiveSheet.PivotTables("PivotTable7"). _
PivotColumnAxis.PivotLines(4), 1

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Format.TextFrame2.TextRange.Characters.Text = "Total Gallons Used"
With Selection.Format.TextFrame2.TextRange.Characters(1, 18).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 5).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(6, 13).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = "Grand Total"
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Gallons"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Gallons"
With Selection.Format.TextFrame2.TextRange.Characters(1, 7).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 7).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
 
Upvote 0
However when I run it like this it works correct, the only thing is if a user acesses the pivot field list and changes it from there and for some reason it does not go in the value part the code will not work. Thanks again for your help.

'Dim lX As Long

'With ActiveSheet.PivotTables(1)
' Debug.Print "PivotFields.Count = " & .PivotFields.Count 'debugging
' .PivotFields(1).Orientation = xlRowField 'First field will be visible
' For lX = 2 To .PivotFields.Count
' .PivotFields(lX).Orientation = xlHidden
' Next
' End With
' Dim lX As Long

' With ActiveSheet.PivotTables(1)
' For lX = 1 To .PivotFields.Count
' .PivotFields(lX).Orientation = xlHidden
' Next
' End With
On Error Resume Next
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Haz Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Non-Haz Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Ink Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Water Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Gas Intensity"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Electric Intensity") _
.Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Haz (lbs)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Non-Haz (Lbs)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Ink (Gallons)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Water (Gallons)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Gas (Therms)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Electric (kWh)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Sum of Output (Packages)"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Output Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Electricity Percent Change" _
).Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Gas Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Water Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Ink Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Non-Haz Percent Change"). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable7").PivotFields("Haz Percent Change"). _
Orientation = xlHidden

ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Year")
.PivotItems("2010 Data").Visible = True
.PivotItems("2011 Data").Visible = True
.PivotItems("2012 Data").Visible = True
.PivotItems("2013 Data").Visible = True
End With
' With ActiveSheet.PivotTables("PivotTable7").PivotFields("Region")
' .Orientation = xlRowField
' .Position = 1
'End With
'With ActiveSheet.PivotTables("PivotTable7").PivotFields("Site")
' .Orientation = xlRowField
' .Position = 2
' End With
' With ActiveSheet.PivotTables("PivotTable7").PivotFields("Year")
' .Orientation = xlRowField
' .Position = 3
' End With
' With ActiveSheet.PivotTables("PivotTable7").PivotFields("Year")
' .Orientation = xlColumnField
' .Position = 1
'End With


Select Case ComboBox1
Case "Water":

ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("Water (Gallons)"), "Sum of Water (Gallons)", xlSum
ActiveSheet.PivotTables("PivotTable7").PivotFields("Site").AutoSort _
xlDescending, "Sum of Water (Gallons)", ActiveSheet.PivotTables("PivotTable7"). _
PivotColumnAxis.PivotLines(4), 1

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Format.TextFrame2.TextRange.Characters.Text = "Total Gallons Used"
With Selection.Format.TextFrame2.TextRange.Characters(1, 18).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 5).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(6, 13).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = "Grand Total"
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Gallons"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Gallons"
With Selection.Format.TextFrame2.TextRange.Characters(1, 7).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 7).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
Case "Gas Intensity":

ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("Gas Intensity"), "Sum of Gas Intensity", xlSum

ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Gas Intensity"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Gas Intensity"
With Selection.Format.TextFrame2.TextRange.Characters(1, 14).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 14).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Therms/Page or Therms/Package"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Therms/Page or Therms/Package"
With Selection.Format.TextFrame2.TextRange.Characters(1, 29).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 29).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
 
Upvote 0
This code will hide only pivotfields in the value area of the Pivot Table that have "Sum of" or "Percent Change" as part of their names. I think that is what you want to have happen.
Code: 
    Dim lX As Long

    With ActiveSheet.PivotTables(1)
        For lX = .DataFields.Count To 1 Step -1
            'Debug.Print .DataFields(lX).Name, .DataFields(lX).Caption 'uncomment for debugging
            If InStr(.DataFields(lX).Name, "Sum of") > 0 Or InStr(.DataFields(lX).Name, "Percent Change") > 0 Then
                .DataFields(lX).Orientation = xlHidden
            End If
        Next
    End With

If that didn't help:
What error message is shown when the program stops on the red code.
After you run the block of code that hides the specific pivotfields are there any visible fields left in the pivottable?
Is all of the code in your last post in the Form module?

The original code should have been:
 
Upvote 0
That worked perfectly. Thank you! I think this is kind of the same question, but for this code the user is selecting the region in which they would like to look at, I was wondering if there was a quicker way to turn them all off at once and then turn one back on instead of turning them all off in each section of the code.

Select Case ComboBox2
Case "Central":
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Region")
.PivotItems("Central").Visible = True
.PivotItems("Eastern").Visible = False
.PivotItems("North American").Visible = False
.PivotItems("Printing").Visible = False
.PivotItems("Western").Visible = False
'.PivotItems("Central").Visible = True
End With
Case "Eastern":

ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Region")
.PivotItems("Eastern").Visible = True
.PivotItems("Central").Visible = False
'.PivotItems("Eastern").Visible = False
.PivotItems("North American").Visible = False
.PivotItems("Printing").Visible = False
.PivotItems("Western").Visible = False

End With

Case "North American":
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Region")
.PivotItems("North American").Visible = True
.PivotItems("Central").Visible = False
.PivotItems("Eastern").Visible = False
'.PivotItems("North American").Visible = False
.PivotItems("Printing").Visible = False
.PivotItems("Western").Visible = False

End With
Case "Printing":

ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Region")
.PivotItems("Printing").Visible = True
.PivotItems("Central").Visible = False
.PivotItems("Eastern").Visible = False
.PivotItems("North American").Visible = False
'.PivotItems("Printing").Visible = False
.PivotItems("Western").Visible = False

End With

Case "Western":

ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Region")
.PivotItems("Western").Visible = True
.PivotItems("Central").Visible = False
.PivotItems("Eastern").Visible = False
.PivotItems("North American").Visible = False
.PivotItems("Printing").Visible = False
'.PivotItems("Western").Visible = False

End With
Case "Show All"
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Region")
.PivotItems("Western").Visible = True
.PivotItems("Central").Visible = True
.PivotItems("Eastern").Visible = True
.PivotItems("North American").Visible = True
.PivotItems("Printing").Visible = True
End With

End Select
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Flexible macro to toggle Pivot table data fields on and off
Replies
1
Views
623
Domenic
D
A
  • Locked
  • Question
Macro to toggle data fields on pivot table
Replies
2
Views
468
Fluff
Fluff
C
  • Solved
Pivot Table Macro
Replies
2
Views
614
chaddres
C
S
  • Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
623
Steve48
S
K
  • Question
Pivot Tables - removing then adding fields using vba
Replies
1
Views
750
keiranwyllie
K

Forum statistics

Threads
1,214,571
Messages
6,120,302
Members
448,954
Latest member
EmmeEnne1979

Share this page

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