Chart formatting macro getting an "object required" error

Question

I am not sure where this error comes from, but it is thrown even if I comment out everything after the data assignment.

I am new to VBA and have tried many variations of this code and spent hours on it in the past few days, but haven't been able to avoid this problem.

Sub fullPageLine()

Dim rng As Range
Dim cht As Object

'Data range for the chart
 Set rng = Selection

'Create a chart
Set cht = ActiveSheet.Shapes.AddChart2(227, xlLine).Select

'Give chart some data
cht.Chart.SetSourceData Source:=rng

cht.Activate

'Reposition Title
    With ActiveChart.ChartTitle
      .Left = 24.632
      .Top = 6

    End With

        'Format x axis
            ActiveChart.Axes(xlCategory).Select
            With Selection.Format.TextFrame2.TextRange.Font
                .NameComplexScript = "Arial"
                .NameFarEast = "Arial"
                .Name = "Arial"
            End With
            Selection.Format.TextFrame2.TextRange.Font.Size = 7

        'Format y axis
            ActiveSheet.ChartObjects("cht").Activate
            ActiveChart.Axes(xlValue).Select
            With Selection.Format.TextFrame2.TextRange.Font
                .NameComplexScript = "Arial"
                .NameFarEast = "Arial"
                .Name = "Arial"
            End With
            Selection.Format.TextFrame2.TextRange.Font.Size = 7

        'Format title
            ActiveSheet.ChartObjects("cht").Activate
            ActiveChart.ChartTitle.Select
                With Selection.Format.TextFrame2.TextRange.Font
                    .NameComplexScript = "Arial"
                    .NameFarEast = "Arial"
                    .Name = "Arial"
                End With
                Selection.Format.TextFrame2.TextRange.Font.Size = 8.4
                Selection.Left = 23.632
                Selection.Top = 6

        'Format legend
            ActiveSheet.ChartObjects("cht").Activate
            ActiveChart.Legend.Select
            With Selection.Format.TextFrame2.TextRange.Font
                .NameComplexScript = "Arial"
                .NameFarEast = "Arial"
                .Name = "Arial"
            End With
            Selection.Format.TextFrame2.TextRange.Font.Size = 7

        'Change chart series fill color
                ActiveSheet.ChartObjects("cht").Activate
                With ActiveChart.FullSeriesCollection(2).Format.Line
                    .Visible = msoTrue
                    .ForeColor.ObjectThemeColor = msoThemeColorAccent2
                    .ForeColor.TintAndShade = 0
                    .ForeColor.Brightness = 0
                    .Transparency = 0
                End With
                ActiveSheet.ChartObjects("cht").Activate
                With ActiveChart.FullSeriesCollection(3).Format.Line
                    .Visible = msoTrue
                    .ForeColor.ObjectThemeColor = msoThemeColorText1
                    .ForeColor.TintAndShade = 0
                    .ForeColor.Brightness = 0
                    .Transparency = 0
                End With
                ActiveSheet.ChartObjects("cht").Activate
                With ActiveChart.FullSeriesCollection(4).Format.Line
                    .Visible = msoTrue
                    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
                    .ForeColor.TintAndShade = 0
                    .ForeColor.Brightness = -0.5
                    .Transparency = 0
                End With
                ActiveSheet.ChartObjects("cht").Activate
                With ActiveChart.FullSeriesCollection(5).Format.Line
                    .Visible = msoTrue
                    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
                    .ForeColor.TintAndShade = 0
                    .ForeColor.Brightness = -0.349999994
                    .Transparency = 0
                End With                 

End Sub

I am looking to produce a chart with my specified color and formatting preferences, but this macro as is only produces the default formatted excel chart from the data I select.

1) What line throws the error? 2) See [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Aug 01 '19 at 19:49
It doesn't give me a specific line, which further confused me. I had avoided select, but had been toggling between a few different options to see if anything would work. I really appreciate the help! — ahb23, Aug 01 '19 at 19:56
`cht.Chart.SetSourceData Source:=rng` is the line producting an error for me. You can actually skip this line as it's written since it already adds the chart from your selection. — Mark S., Aug 01 '19 at 19:58
First, I'm surprised this line doesn't throw an error: `Set cht = ActiveSheet.Shapes.AddChart2(227, xlLine).Select` (remove the `.Select` from that). Then try just `cht.SetSourceData ...`. Also, read through [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — BruceWayne, Aug 01 '19 at 20:04
Still got the same error after trying both of those. As I said I'm very new to this. Thanks for the help, I will read through both those threads. — ahb23, Aug 01 '19 at 20:08
I'm hashing through 'getting it to work' but it's not going to be pretty. I'm about halfway — Mark S., Aug 01 '19 at 20:16

score 0 · Answer 1 · answered Aug 01 '19 at 20:22

I didn't take the time to really clean this up or anything but I got it working as far as the collection(5). There's a lot to improve here, but that's something I'll let other people tweak and edit to help you learn. right now function, then form.

Sub fullPageLine()

Dim rng As Range
Dim cht As Object

'Data range for the chart
Set rng = Selection
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
'Create a chart
Set cht = Selection

'Give chart some data
'cht.Chart.SetSourceData Source:=rng


'Reposition Title
    With cht
      .Left = 24.632
      .Top = 6

    End With

    'Format x axis
        ActiveChart.ChartArea.Select
        With Selection
            .Format.TextFrame2.TextRange.Font.Name = "Arial"
            .Format.TextFrame2.TextRange.Font.Size = 7
        End With

    'Format title
        ActiveChart.ChartTitle.Font.Size = 8.4
            ActiveChart.ChartTitle.Left = 23.632
            ActiveChart.ChartTitle.Top = 6

    'Format legend
        ActiveChart.Legend.Select
        With Selection.Format.TextFrame2.TextRange.Font
            .NameComplexScript = "Arial"
            .NameFarEast = "Arial"
            .Name = "Arial"
        End With
        Selection.Format.TextFrame2.TextRange.Font.Size = 7

    'Change chart series fill color
            With ActiveChart.FullSeriesCollection(2).Format.Line
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorAccent2
                .ForeColor.TintAndShade = 0
                .ForeColor.Brightness = 0
                .Transparency = 0
            End With
            With ActiveChart.FullSeriesCollection(3).Format.Line
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorText1
                .ForeColor.TintAndShade = 0
                .ForeColor.Brightness = 0
                .Transparency = 0
            End With
            With ActiveChart.FullSeriesCollection(4).Format.Line
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorBackground1
                .ForeColor.TintAndShade = 0
                .ForeColor.Brightness = -0.5
                .Transparency = 0
            End With
            With ActiveChart.FullSeriesCollection(5).Format.Line
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorBackground1
                .ForeColor.TintAndShade = 0
                .ForeColor.Brightness = -0.349999994
                .Transparency = 0
            End With


End Sub

Chart formatting macro getting an "object required" error

1 Answers1