Pivot chart formats

Question

very VBA-knowledgeable people,

I have a pivot chart going on, and I wrote a sub to format the series representation. This chart contains four series and is connected to a slicer.

The problem is that the formats aren't working for some of the slicer's buttons, because one of the series formats disappear. This series format should be a grey line; the data points are there, but the line and fill color are absent.

I already debuged the thing and used the watch to check what's going on, but everything's fine and running as it should. When I F8 the macro, after the series that isn't working, I try using the mouse to force the color on the graph line and it works.

Do you have any suggestion of where should I look for the problema? Does this also happen to your pivot charts?

I wrote this code:

Dim srs_name As String   
Dim srs As Integer


ActiveSheet.ChartObjects("Diagramm 7").Activate

         'formatting Shipped Qty series
srs_name = "Shipped qty"
On Error Resume Next
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(255, 192, 0)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(255, 192, 0)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

         'formatting Order series
srs_name = "Order"
On Error Resume Next
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(91, 155, 213)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(91, 155, 213)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

        'formatting Sales series
srs_name = "Sales"
On Error Resume Next
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlLine
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(165, 165, 165)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(165, 165, 165)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

       'formatting Transport series
srs_name = "Transport"
On Error Resume Next
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(237, 125, 49)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(237, 125, 49)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
Else
End If

       'formatting a 5th series, if needed
srs = ActiveSheet.ChartObjects("Diagramm 7").Chart.SeriesCollection.Count
If srs > 4 Then
    ActiveChart.SeriesCollection(5).ChartType = xlArea
    ActiveChart.SeriesCollection(5).Format.Fill.ForeColor.RGB = RGB(222, 235, 247)
    ActiveChart.SeriesCollection(5).Format.Line.ForeColor.RGB = RGB(222, 235, 247)
    ActiveChart.SeriesCollection(5).AxisGroup = 1
End If

If you comment out those instances of `On Error Resume Next`, do you get an error? If so, what is the message and what line throws it? — BigBen, Sep 18 '19 at 14:00
Hello, @BigBen, i don't get any error when I comment out those instances. — SSGrace, Sep 18 '19 at 14:02
@SSGrace The way you used `On Error Resume Next` is the worst practice (it hides all error massages but the errors still occur, and you will never notice if something fails in your code). Never use it alone. For further information you might benefit of reading [VBA Error Handling – A Complete Guide](https://excelmacromastery.com/vba-error-handling) — Pᴇʜ, Sep 18 '19 at 14:03
Hi, @Pᴇʜ, I didn't know, but I'll watch it from now on, thanks for the info! — SSGrace, Sep 18 '19 at 14:07
Besides the on-error-resume-next, do you have other suggestions, @Pᴇʜ? — SSGrace, Sep 19 '19 at 07:33

score 1 · Accepted Answer · answered Sep 19 '19 at 11:21

SOLVED: I just needed to add a ActiveChart.SeriesCollection(srs_name).Format.Line.Visible = True line.

This is how it got:

Dim srs_name As String   
Dim srs As Integer

ActiveSheet.ChartObjects("Diagramm 7").Activate

         'formatting Shipped Qty series
srs_name = "Shipped qty"
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(255, 192, 0)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(255, 192, 0)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

         'formatting Order series
srs_name = "Order"
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(91, 155, 213)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(91, 155, 213)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

        'formatting Sales series
srs_name = "Sales"
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlLine
    ActiveChart.SeriesCollection(srs_name).Format.Line.Visible = True
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(165, 165, 165)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(165, 165, 165)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
End If

       'formatting Transport series
srs_name = "Transport"
If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then
    ActiveChart.SeriesCollection(srs_name).ChartType = xlColumnStacked
    ActiveChart.SeriesCollection(srs_name).Format.Fill.ForeColor.RGB = RGB(237, 125, 49)
    ActiveChart.SeriesCollection(srs_name).Format.Line.ForeColor.RGB = RGB(237, 125, 49)
    ActiveChart.SeriesCollection(srs_name).AxisGroup = 1
Else
End If

       'formatting a 5th series, if needed
srs = ActiveSheet.ChartObjects("Diagramm 7").Chart.SeriesCollection.Count
If srs > 4 Then
    ActiveChart.SeriesCollection(5).ChartType = xlArea
    ActiveChart.SeriesCollection(5).Format.Fill.ForeColor.RGB = RGB(222, 235, 247)
    ActiveChart.SeriesCollection(5).Format.Line.ForeColor.RGB = RGB(222, 235, 247)
    ActiveChart.SeriesCollection(5).AxisGroup = 1
End If

Pᴇʜ · Answer 2 · 2019-09-19T12:10:58.400

I think If Not ActiveChart.SeriesCollection(srs_name) Is Nothing Then will throw an exeption if the series name does not exist. Instead I recommend a function for this to test if SeriesExists.

Also I recommend not to use Activate and ActiveChart (see How to avoid using Select in Excel VBA). Instead specify the chart by its name.

Note that you don't need to count the number of series to test if there is a 5ᵗʰ one. You can just use SeriesExists(MyChart, 5) to test this.

I recommend something like the following:

Option Explicit

Public Sub FormatCharts()
    Dim ws As Worksheet
    Set ws = ActiveSheet 'better something like ThisWorkbook.Worksheets("Tabelle 1")

    Dim MyChart As Chart
    Set MyChart = ws.ChartObjects("Diagramm 7").Chart

    Dim srs_name As String

    'formatting Shipped Qty series
    srs_name = "Shipped qty"
    If SeriesExists(MyChart, srs_name) Then
        With MyChart.SeriesCollection(srs_name)
            .ChartType = xlColumnStacked
            .Format.Fill.ForeColor.RGB = RGB(255, 192, 0)
            .Format.Line.ForeColor.RGB = RGB(255, 192, 0)
            .AxisGroup = 1
        End With
    End If

   'formatting Order series
    srs_name = "Order"
    If SeriesExists(MyChart, srs_name) Then
        With MyChart.SeriesCollection(srs_name)
            .ChartType = xlColumnStacked
            .Format.Fill.ForeColor.RGB = RGB(91, 155, 213)
            .Format.Line.ForeColor.RGB = RGB(91, 155, 213)
            .AxisGroup = 1
        End With
    End If

    'formatting Sales series
    srs_name = "Sales"
    If SeriesExists(MyChart, srs_name) Then
        With MyChart.SeriesCollection(srs_name)
            .ChartType = xlLine
            .Format.Line.Visible = True
            .Format.Fill.ForeColor.RGB = RGB(165, 165, 165)
            .Format.Line.ForeColor.RGB = RGB(165, 165, 165)
            .AxisGroup = 1
        End With
    End If

    'formatting Transport series
    srs_name = "Transport"
    If SeriesExists(MyChart, srs_name) Then
        With MyChart.SeriesCollection(srs_name)
            .ChartType = xlColumnStacked
            .Format.Fill.ForeColor.RGB = RGB(237, 125, 49)
            .Format.Line.ForeColor.RGB = RGB(237, 125, 49)
            .AxisGroup = 1
        End With
    End If

    'formatting a 5th series, if needed
     If SeriesExists(MyChart, 5) Then 
        With MyChart.SeriesCollection(5)
            .ChartType = xlArea
            .Format.Fill.ForeColor.RGB = RGB(222, 235, 247)
            .Format.Line.ForeColor.RGB = RGB(222, 235, 247)
            .AxisGroup = 1
        End With
    End If
End Sub

Private Function SeriesExists(InChart As Chart, SeriesName As Variant) As Boolean
    Dim TestSeries As Series
    On Error Resume Next
    Set TestSeries = InChart.SeriesCollection(SeriesName)
    On Error GoTo 0
    SeriesExists = Not TestSeries Is Nothing
End Function

Pivot chart formats

2 Answers2