Recorded macro breaks on Legend formatting

Question

Sub Total()
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 227
ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(1).Select
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
End With
ActiveChart.Legend.LegendEntries(5).Select
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 255, 0)
    .Transparency = 0
End With
ActiveChart.Legend.LegendEntries(6).Select
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = RGB(0, 176, 80)
    .Transparency = 0
End With
End Sub

I tried to recreate this macro multiple times to mould it to my need, so I ran it once to test it and it broke once it got to the With Selection.Format.Line, giving me this error. Which part of the macro is causing this error?

Edit: Shai Rado posted perfect fix Recorded macro breaks on Legend formatting

Stupid answer: the `With Selection.Format.Line` is breaking your code. Looks like you are starting with "Chart 19", selecting the legend and then selecting the first legend entry. Make sure there is actually a legend entry and that the LegendEntries collection is 1 based (not 0) — CodeJockey, Jan 25 '17 at 14:38
You can use With ActiveChart.Legend.LegendEntries(5).Format.Line, remove the selection. Select causes errors — Lowpar, Jan 25 '17 at 14:39
@Lowpar Excellent Point. For more on avoiding `.Select`: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros?rq=1 — CodeJockey, Jan 25 '17 at 14:41
@CodeJockey Sorry, I tried removing Select before posting the question, same story. I did `With ActiveChart.Legend.LegendEntries(1).Format.Line` and got the same error. Not only that, but to test where error cropped up, re-opened unedited copy of the workbook and pasted the code back into the module to force vba to perform the macro the same way I did it when recording and even then it broke. — Atlas, Jan 25 '17 at 14:43

score 1 · Accepted Answer · answered Jan 25 '17 at 14:47

The code below sets the ChartObject of "Chart 19" to a variable, and later just modify the needed parameters without needing to use ActiveChart, Selection and Select.

Option Explicit

Sub Total()

Dim ChtObj As ChartObject

Set ChtObj = Worksheets("Sheet4").ChartObjects("Chart 19") '<-- modify "Sheet4" to your sheet's name where you have your charts

With ChtObj
    With .Chart.SeriesCollection(1).Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .Transparency = 0
    End With

    With .Chart.SeriesCollection(5).Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 0)
        .Transparency = 0
    End With

    With .Chart.SeriesCollection(6).Format.Line
       .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 80)
        .Transparency = 0
    End With            
End With

End Sub

Recorded macro breaks on Legend formatting

1 Answers1