CODEWITHSUNDEEP
vba
Arkadiusz
Arkadiusz

Reputation: 427

Filling graph with colour

I want to format series on my graph, I registered macro:

ActiveChart.FullSeriesCollection(1).Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(146, 208, 80)
    .Transparency = 0
    .Solid
End With

and then I put it into my code:

        With Wykres
            .ShowLegendFieldButtons = False
            .ShowValueFieldButtons = False
            .ShowAxisFieldButtons = False
            .ShowAllFieldButtons = False
            .Legend.Delete
            .ChartStyle = 340
            .SetElement (msoElementChartTitleAboveChart)
            .ChartTitle.Text = "Ilość w podziale na województwa"
            .SetElement (msoElementDataLabelOutSideEnd)
            .Parent.RoundedCorners = True
            .ChartArea.Format.Line.ForeColor.RGB = KolorUzytkownika
            .FullSeriesCollection.Format.Fill.ForeColor.RGB = KolorUzytkownika
        End With

but something is wrong with the last line of my code. I received message: "Object doesn't support this property or method". I use late binding but I guess it doesn't matter here.

Upvotes: 0

Views: 1364

Answers (2)

Ryszard Jędraszyk
Ryszard Jędraszyk

Reputation: 2412

This will loop through and format all series on chart belonging to 1st ChartObject on 1st Worksheet. In your case, replace Worksheets(1).ChartObjects(1).Chart with Wykres:

Sub formatChartSeries()

    Dim s

    For Each s In Worksheets(1).ChartObjects(1).Chart.SeriesCollection
        s.Format.Fill.ForeColor.RGB = RGB(146, 208, 80) 'format fill / marker
        s.Format.Line.ForeColor.RGB = RGB(146, 208, 80) 'format border / line
    Next s

End Sub

Above solution is compatible with Excel 2010 which doesn't support FullSeriesCollection.

Upvotes: 1

cyboashu
cyboashu

Reputation: 10433

.FullSeriesCollection is of type Collection. You can't access/modify a property of type Series for Collection type. Hence the error.

enter image description here

You need to first access the item from the collection and then all the public properties and methods of that item's type become accessible.

.FullSeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)

Upvotes: 6

Related Questions