Two issues - Determine Currently Visible Axis and Skip Formatting for Chart Data

[ShaneW]

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:

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:

' 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

 

[ShaneW]

Sorry, I forgot to mention, Windows 10, Excel 2013.

 

[mole999]

my simplistic way is to create 4 dummy values, one for each and give them the numeric value of zero, then when you run your scenarios you will ALWAYS have a result, so no missing columns

 

[ShaneW]

my simplistic way is to create 4 dummy values, one for each and give them the numeric value of zero, then when you run your scenarios you will ALWAYS have a result, so no missing columns

That would introduce an extra value in the data set though which would change the divisor for the percentages for other calculations. Is there a way to force a dummy value into the pivot table? I've never tried that.

 

[mole999]

I think the pivot can't be added to, and I would wonder if maybe a BLANK can be inserted, it was something I have used to stop pivots collapsing, but never thought about what happens if I want an average, maybe average minus one divisor so that it would be numerically correct

 

[ShaneW]

Well, I solved the second problem noted above. I still don't know how to get the names of axis fields that are currently visible, but since ultimately I wanted to set the pivottable to have rows selected by an end user via button, I found this worked:

Worksheets("ELA and Math Achievement Level").PivotTables("PivotTable4").AddFields _
        ColumnFields:=pColumn1, _
        RowFields:=Array(pField1, pField2)

As it clears out all rows and columns before it operates, then adds in the columns and fields the buttons have populated in pColumn1, pField1, pField2.