CODEWITHSUNDEEP
vbaexcel
rpecas
rpecas

Reputation: 187

Read Chart Properties using VBA

I'm using VBA to create some graphics I need to do.

Basically, what I want to do is to create the 1st series automatically and then, the 2nd series, copy the color and format from the 1st series.

I'm trying to do this code but with no success: 

ActiveChart.SeriesCollection(a - 1).Select
ActiveChart.SeriesCollection(a).Border.ColorIndex = ActiveChart.SeriesCollection(a - 1).Border.ColorIndex
ActiveChart.SeriesCollection(a).MarkerBackgroundColorIndex = ActiveChart.SeriesCollection(a - 1).MarkerBackgroundColorIndex
ActiveChart.SeriesCollection(a).MarkerForegroundColorIndex = ActiveChart.SeriesCollection(a - 1).MarkerForegroundColorIndex
ActiveChart.SeriesCollection(a).MarkerStyle = ActiveChart.SeriesCollection(a - 1).MarkerStyle

Can someone please help me on how to read the properties from the previous series and apply them to the next?

Thanks.

Upvotes: 1

Views: 1013

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

In general something like this could work:

Sub Tester()
    Dim Cht as Chart
    Dim a As Series, b As Series, x As Long

    Set cht = ActiveChart
    Set a = cht.SeriesCollection(1)

    For x = 2 To cht.SeriesCollection.Count
        Set b = cht.SeriesCollection(x)
        b.Border.Color = a.Border.Color
        b.MarkerBackgroundColor = a.MarkerBackgroundColor
        b.MarkerForegroundColor = a.MarkerForegroundColor
        b.MarkerStyle = a.MarkerStyle
    Next x

End Sub

Note however that some properties will not be read unless the first series was manually formatted, and is not just the "default" format.

Eg: see Fill and Border color property of data point marker (scatter or line) Excel VBA for similar problem.

Upvotes: 1

Related Questions