CODEWITHSUNDEEP
vbaexcel
Sanjay Singh Negi
Sanjay Singh Negi

Reputation: 9

Running loop in excel vba 2016 for changing series in charts

I have the following macro and I wish to loop the following program for 500 charts starting from 1. 

Sub Arrow()
'
' Arrow Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveSheet.ChartObjects("Chart 459").Activate

    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(192, 0, 0)
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 2.5
    End With
    Selection.Format.Line.EndArrowheadStyle = msoArrowheadTriangle
    With Selection.Format.Line
        .EndArrowheadLength = msoArrowheadLengthMedium
        .EndArrowheadWidth = msoArrowheadWide
    End With
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent5
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.5
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 2.5
    End With
    Selection.Format.Line.EndArrowheadStyle = msoArrowheadTriangle
    With Selection.Format.Line
        .EndArrowheadLength = msoArrowheadLengthMedium
        .EndArrowheadWidth = msoArrowheadWide
    End With
End Sub

Upvotes: 0

Views: 242

Answers (1)

Jean-Pierre Oosthuizen
Jean-Pierre Oosthuizen

Reputation: 2683

I agree with @Jeeped. What you want is not difficult. However, moving from Select etc to Index based takes some learning.

the code below should do what you want. It worked for me in Office 2010, which uses SeriesCollection(1) instead of FullSeriesCollection(1)

Sub Arrow() ' ' Arrow Macro ' ' Keyboard Shortcut: Ctrl+q ' ActiveSheet.ChartObjects("Chart 459").Activate

    Dim i As Long
    Dim cht As Chart

    For i = 1 To ActiveWorkbook.Charts.Count

        Set cht = ActiveWorkbook.Charts(i)

        With cht.FullSeriesCollection(1).Format.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(192, 0, 0)
            .Transparency = 0
            .Weight = 2.5
            .EndArrowheadStyle = msoArrowheadTriangle
            .EndArrowheadLength = msoArrowheadLengthMedium
            .EndArrowheadWidth = msoArrowheadWide
        End With

        With cht.FullSeriesCollection(2).Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent5
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = -0.5
            .Transparency = 0
            .Visible = msoTrue
            .Weight = 2.5
            .EndArrowheadStyle = msoArrowheadTriangle
            .EndArrowheadLength = msoArrowheadLengthMedium
            .EndArrowheadWidth = msoArrowheadWide
        End With

    Next i

End Sub

Now you know how to use a For Loop and Index based references.

Upvotes: 1

Related Questions