CODEWITHSUNDEEP
arraysvbachartsformat
RebaS
RebaS

Reputation: 47

Formatting charts in a chart group

I have five column charts, one line chart and three pie charts on a dashboard page. I have grouped like charts and named each group. The five column charts are in a group named "CleanCharts". All charts in a group have the same series ("Incomplete", "Complete", and "NA"). I'd like a sub to format all of the charts in a named group to identically fill and border the series collection.

I've read many forums on charts, chartobjects, series collections, looping to format charts and I've tried different ways to approach this over several days. I thought grouping the charts and naming the chartgroups was a good idea, but it's not working. Honestly, I'm more confused now than when I started.

Sub FormatChartGrp()

Dim ws As Worksheet
Dim dash As Worksheet
Dim Cht As Chart

Set dash = Sheets("Dashboard")

With dash.Shapes.Range(Array("CleanCharts"))

With ActiveChart.FullSeriesCollection(1)
        With .Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 192, 0) 'Not Applicable Orange
            .Solid
        End With
        With .Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorText1
        End With
      End With
With ActiveChart.FullSeriesCollection(2)
        With .Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 176, 80) 'Complete green
            .Solid
        End With
        With .Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorText1
        End With
    End With
With ActiveChart.FullSeriesCollection(3)
        With .Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0) 'Incomplete Red
            .Solid
        End With
        With .Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorText1
        End With
    End With
End With
End Sub

When I try my latest attempt I get an error message "Block variable or with block variable not set". Please help me understand what I'm missing. Thank you!

Upvotes: 0

Views: 123

Answers (1)

Domenic
Domenic

Reputation: 8124

The GroupItems property of the ShapeRange object can be used to access the shapes in the specified group. Then we can loop through each group item in GroupItems.

Option Explicit

Sub FormatChartGrp()

    Dim dash As Worksheet
    Dim groupItem As Shape

    Set dash = Sheets("Dashboard")

    For Each groupItem In dash.Shapes.Range(Array("CleanCharts")).GroupItems

        With groupItem.Chart.FullSeriesCollection(1)
            With .Format.Fill
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 192, 0) 'Not Applicable Orange
                .Solid
            End With
            With .Format.Line
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorText1
            End With
        End With
        With groupItem.Chart.FullSeriesCollection(2)
            With .Format.Fill
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 176, 80) 'Complete green
                .Solid
            End With
            With .Format.Line
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorText1
            End With
        End With
        With groupItem.Chart.FullSeriesCollection(3)
            With .Format.Fill
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0) 'Incomplete Red
                .Solid
            End With
            With .Format.Line
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorText1
            End With
        End With

    Next groupItem

End Sub

EDIT

Note, however, your macro can be re-written as follows...

Option Explicit

Sub FormatChartGrp()

    Dim dash As Worksheet
    Dim groupIndex As Long
    Dim seriesIndex As Long

    Set dash = Sheets("Dashboard")

    With dash.Shapes.Range(Array("CleanCharts")).GroupItems
        For groupIndex = 1 To .Count
            With .Item(groupIndex).Chart.FullSeriesCollection
                For seriesIndex = 1 To .Count
                    With .Item(seriesIndex)
                        With .Format.Fill
                            .Visible = msoTrue
                            Select Case seriesIndex
                                Case 1
                                    .ForeColor.RGB = RGB(255, 192, 0) 'Not Applicable Orange
                                Case 2
                                    .ForeColor.RGB = RGB(255, 176, 80) 'Complete green
                                Case Else
                                    .ForeColor.RGB = RGB(255, 0, 0) 'Incomplete Red
                            End Select
                            .Solid
                        End With
                        With .Format.Line
                            .Visible = msoTrue
                            .ForeColor.ObjectThemeColor = msoThemeColorText1
                        End With
                    End With
                Next seriesIndex
            End With
        Next groupIndex
    End With

End Sub

For a group of pie charts, you'll need to iterate through each point within the series for each chart in the group. Here's an example that does that, and hides the third point, as per your request. 

Dim dash As Worksheet
Dim groupIndex As Long
Dim pointIndex As Long

Set dash = Sheets("Dashboard")

With dash.Shapes.Range(Array("ChartGroupName")).GroupItems 'change the name of the group accordingly
    For groupIndex = 1 To .Count
        With .Item(groupIndex).Chart.FullSeriesCollection(1)
            For pointIndex = 1 To .Points.Count
                With .Points(pointIndex)
                    If pointIndex = 3 Then
                        .Format.Fill.Visible = msoFalse
                        .Format.Line.Visible = msoFalse
                    Else
                        With .Format.Fill
                            .Visible = msoTrue
                            Select Case pointIndex
                                Case 1
                                    .ForeColor.RGB = RGB(255, 192, 0) 'Not Applicable Orange
                                Case 2
                                    .ForeColor.RGB = RGB(255, 176, 80) 'Complete green
                            End Select
                            .Solid
                        End With
                        With .Format.Line
                            .Visible = msoTrue
                            .ForeColor.ObjectThemeColor = msoThemeColorText1
                        End With
                    End If
                End With
            Next pointIndex
        End With
    Next groupIndex
End With

Upvotes: 2

Related Questions