Thread: Creating a Chart

I am designing a macro in order to load a range (Spanning columns a, b, and c, with a variable amount of rows), and came up with the following code. It seems to work fine using Excel 2003, but when running compatibility tests in Excel 2007, I received an error '400'. Thinking it was perhaps an incompatibility with the ChartObjects.Add command, I replaced it with the newer Shapes.AddChart command, but no luck.

Any ideas? The chart code is below.

Sub CreateChart()
Dim rowMax As Integer

rowMax = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row 'UsedRange.Rows.Count

With Sheets(1).ChartObjects.Add(Left:=5, Width:=614, Top:=477, Height:=462).Chart
    .SetSourceData Source:=Sheets(2).Range("A1", "C" & rowMax), PlotBy:=xlColums    
    .ChartType = xlLine
    .Parent.Name = "DataChart"
    .HasTitle = True
    .ChartTitle.Characters.Text = "Title"
    
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X Axis"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y Axis"
    
    .HasDataTable = False
    
    .Axes(xlCategory).HasMajorGridlines = False
    .Axes(xlCategory).HasMinorGridlines = False
    .Axes(xlCategory).TickLabelSpacing = Int(rowMax / 3)
    
    .Axes(xlValue).HasMajorGridlines = True
    .Axes(xlValue).HasMinorGridlines = False
End With
   
End Sub

Hi,

Try the following slight modification. Incidentally, and I'm sure it was a typo but your original said 'PlotBy = xlColums' and not xlColumns and would have failed because of this.

Sub CreateChart()
    Dim rowMax As Integer

    rowMax = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row    'UsedRange.Rows.Count

    With Sheets(1).Shapes.AddChart.Select
        With ActiveChart
            .Parent.Left = 5
            .Parent.Width = 614
            .Parent.Top = 477
            .Parent.Height = 462
            .SetSourceData Source:=Range("A1:C" & rowMax)
            .ChartType = xlLine
            .Parent.Name = "DataChart"
            .HasTitle = True
            .ChartTitle.Characters.Text = "Title"
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X Axis"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y Axis"
            .HasDataTable = False
            .Axes(xlCategory).HasMajorGridlines = False
            .Axes(xlCategory).HasMinorGridlines = False
            .Axes(xlCategory).TickLabelSpacing = Int(rowMax / 3)
            .Axes(xlValue).HasMajorGridlines = True
            .Axes(xlValue).HasMinorGridlines = False

        End With
    End With

End Sub

Hi,

Are you running the macro when the most left hand sheet is the active sheet? The macro refers to Sheets(1) which I always feel is somewhat dangerous in case a user drags the sheet to another position. Far better to explicitly refer to the sheet's name or code name.

HTH