Hello, I've lurked for years and have always been able to find what I needed through searching. So thank you for all the help over the years. This time I'm up against time pressure and still haven't found what I'm looking for.
I have a pivot table and chart that I have created with some code to format the chart the same way every time (because the pivot chart otherwise reverts to default when a different series is selected). There are a couple combinations of slices that result in an element or more of the data series not having anything to display. Like so:
<tbody>
</tbody>
With multiple ethnicities selected in the slicer there will be four columns, Standard Not Met, Standard Nearly Met, Standard Met, Standard Exceeded because there are enough rows to include all four.
The following code crops up an error because (I'm guessing...) the Standard Not Met and Standard Nearly Met don't exist on the pivot table. This is part of a larger chunk of code that includes switching the Legend (Series) between two different values depending on the button clicked:
An Run-time error 1004 crops up, Invalid Parameter. How can I skip the section of code that pertains to the missing series? I tried the On Error Resume Next thing, it didn't work and instead miscolored the sections that are present.
Second, I'm trying to set up some buttons to modify the Axis (Categories) of the PivotTable. But they need to clear out any existing visible fields there. How do you find current visible and turn them off without resorting to simply looping through the whole possible list?
Thank you,
Shane
I have a pivot table and chart that I have created with some code to format the chart the same way every time (because the pivot chart otherwise reverts to default when a different series is selected). There are a couple combinations of slices that result in an element or more of the data series not having anything to display. Like so:
Count | Achievement | |||
Gender | Ethnicity | Standard Met | Standard Exceeded | Grand Total |
Female | Vulcan | 20% | 80% | 100% |
Grand | Total | 20% | 80% | 100% |
<tbody>
</tbody>
With multiple ethnicities selected in the slicer there will be four columns, Standard Not Met, Standard Nearly Met, Standard Met, Standard Exceeded because there are enough rows to include all four.
The following code crops up an error because (I'm guessing...) the Standard Not Met and Standard Nearly Met don't exist on the pivot table. This is part of a larger chunk of code that includes switching the Legend (Series) between two different values depending on the button clicked:
Code:
' Color Standard Exceeded blue
ActiveChart.FullSeriesCollection("Standard Exceeded").Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 240)
.Transparency = 0
.Solid
End With
' Color Standard Met green
ActiveChart.FullSeriesCollection("Standard Met").Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Transparency = 0
.Solid
End With
' Color Standard Nearly Met yellow
ActiveChart.FullSeriesCollection("Standard Nearly Met").Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 255, 0)
.Transparency = 0
.Solid
End With
' Color Standard Not Met red
ActiveChart.FullSeriesCollection("Standard Not Met").Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
An Run-time error 1004 crops up, Invalid Parameter. How can I skip the section of code that pertains to the missing series? I tried the On Error Resume Next thing, it didn't work and instead miscolored the sections that are present.
Second, I'm trying to set up some buttons to modify the Axis (Categories) of the PivotTable. But they need to clear out any existing visible fields there. How do you find current visible and turn them off without resorting to simply looping through the whole possible list?
Thank you,
Shane