Code for Pivot Charts

[tjgrassi]

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​

 

[pbornemeier]

This should work:

Sub SetRegionDisplayByComboBox2()

    Dim lX As Long
    
    Select Case ComboBox2
    Case "Show All"
        'ActiveSheet.ChartObjects("Chart 1").Activate
        With ActiveSheet.PivotTables(1).PivotFields("Region")
            For lX = 1 To .PivotItems.Count
                .PivotItems(lX).Visible = True
            Next
        End With
    Case Else
        With ActiveSheet.PivotTables(1).PivotFields("Region")
            .PivotItems(ComboBox2.Value).Visible = True
            For lX = 1 To .PivotItems.Count
                If .PivotItems(lX).Name <> ComboBox2.Value Then .PivotItems(lX).Visible = False
            Next
        End With
    End Select
End Sub

Please used code tags (see link in mys sig) when posting code.
Changing the name on the ComboBox to something like: cboSelectRegion will make figuring out your code much easier 1 year from now.

 

[tjgrassi]

Thanks for all the help, i'll try to see if I can get the code box to work for me. I've been trying to write a code that is similar to the one you have helped me with but to turn on all the years, each time the user form is run, as a column label field. This code is what works but I'd like to shorten the code as much as possible and also apply it to a row lable of sites.

ActiveSheet.ChartObjects("Chart 1").Activate
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Year")
        .PivotItems("2009 Data").Visible = True
        .PivotItems("2010 Data").Visible = True
        .PivotItems("2011 Data").Visible = True
        .PivotItems("2012 Data").Visible = True
        .PivotItems("2013 Data").Visible = True
    End With

I would also like to apply this to "Sites" in my table, but im sure that it would be the same code just change the words. There are about 20 sites so the code would get pretty long. Thanks again for all your help!

 

[pbornemeier]

This will turn on all items for the "Year" PivotField, no matter whether if it is a row or column. Change "Year" to "Sites" for the "Sites" PivotField items.

    Dim lX As Long
    
    For lX = 1 To ActiveSheet.PivotTables(1).PivotFields("Year").PivotItems.Count
        With ActiveSheet.PivotTables(1).PivotFields("Year")
            .PivotItems(lX).Visible = True
        End With
    Next

 

[tjgrassi]

One last quick question. So for a particular function my user form can show how much of a resource was used in each year for a particular region that has X amount of sites in it. I want to be able to sort the grand total from largest to smallest for the region shown or when all regions are shown the sites are grouped in their region and I want to be able to sort within each region largest to smallest. I do not have the code anymore but when I tried to do it I had to put a number in for the amount of sites in each region to sort and each region has a different number of sites so when i selected the same resource but a different region the code did not work. Thanks

In case that doesnt make sense, I want to sort sites in descending order.

 

[pbornemeier]

I am not entirely sure of your field names that you want to sort on, but here is a generic example:

Sub PivotTableSorts()

    Dim lPivotLineCount As Long
    
    'I used this to sort on a Sum field
    lPivotLineCount = ActiveSheet.PivotTables("PivotTable1").PivotRowAxis.PivotLines.count
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Country").AutoSort _
        xlDescending, "Sum of N", ActiveSheet.PivotTables("PivotTable1").PivotRowAxis. _
        PivotLines(lPivotLineCount), 1
        
    'I used this to sort on 2 row fields
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").AutoSort xlAscending, "Name" 'Primary Sort
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarter").AutoSort xlAscending, "Quarter" 'Secondary Sort
    
    'Look at VBA help on autosort for (a very few more) details on the 'PivotField.AutoSort Method'
    
End Sub

 

[tjgrassi]

I was hoping for something along the lines of this. I know the code might not make sense but it might help show what I need.

'ActiveSheet.PivotTables(1).PivotField("Combobox2.value") _
  '.AutoSort xlDescending, "Sum of combobox2.value"

 

[pbornemeier]

Try

    lPivotLineCount = ActiveSheet.PivotTables("PivotTable1").PivotRowAxis.PivotLines.Count
    ActiveSheet.PivotTables("PivotTable1").PivotFields(frmWhatever.Combobox2.Value).AutoSort _
        xlDescending, "Sum of " & frmWhatever.Combobox2.Value, ActiveSheet.PivotTables("PivotTable1").PivotRowAxis. _
        PivotLines(lPivotLineCount), 1

If this code is on the form then you can eliminate the frmWhatever. items

 

[tjgrassi]

At first it said that 1pivotlinecount varaible was not found and then i tried to define it as variant and then long, it told me i had a complie error: syntax error. Also the code is in the user form, but i'm not sure which frmWhatever i should take out? Thanks

 

[pbornemeier]

Try this:

    Dim lPivotLineCount as Long ' starts with lower case l - because it is a Long variable
    lPivotLineCount = ActiveSheet.PivotTables(1).PivotRowAxis.PivotLines.Count

    ActiveSheet.PivotTables(1).PivotFields(Combobox2.Value).AutoSort _
        xlDescending, "Sum of " & Combobox2.Value, ActiveSheet.PivotTables("PivotTable1").PivotRowAxis. _
        PivotLines(lPivotLineCount), 1

 

[tjgrassi]

Now I am getting a run time error '1004: unable to get the pivot table properties of the worksheet class? This occurs at the 3rd line of code from the post above.