2 Axis Charts with VB in Excel 1

[DreamerZ]

I have recorded the following VBA below. No errors in recording. Upon running the macro, however, the code errors on the bolded lines:
"Method Axes of Object_Chart failed" is the error

Sub view_MeasurementCharts()
    
    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"
    ActiveChart.SetSourceData Source:=Sheets("MeasurementData").Range( _
        "D1:E3,M1:M3"), PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet5"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "" '//Chart Title
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "" 'Left Axis
Title
        [b].Axes(xlCategory, xlSecondary).HasTitle = False
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "" 'Right
Axis Title[/b]
    End With
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    [b]ActiveChart.Axes(xlValue, xlSecondary).Select
    With ActiveChart.Axes(xlValue, xlSecondary)[/b]
        .MinimumScale = 0
        .MaximumScale = 25
        .MinorUnitIsAuto = True
        .MajorUnit = 5
        .Crosses = xlCustom
        .CrossesAt = 0
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    Selection.TickLabels.NumberFormat = "0"
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = 175
        .MaximumScale = 210
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlCustom
        .CrossesAt = 0
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
End Sub

It's not setting up the secondary axis properly...basically, not at all even
though it recorded it. xlSecondary is a keyword for the Axes Method.
Something else I have to do to get the secondary axis working?

Thx,


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[SkipVought]

D,

1. Can't start with a split range -- add it later in code
2. starts out as a Chart object and becomes a ChartObject object when it's location is made a sheet (BTW I changed sheet names so I could run)

check out this modified code. you will need to tweek...

Sub view_MeasurementCharts()
    Dim ch1 As ChartObject
    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"
    ActiveChart.SetSourceData Source:=Sheet1.Range( _
        "D1:E3"), PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
    Set ch1 = ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count)
    With ch1.Chart
        .HasTitle = True
        .ChartTitle.Characters.Text = "" '//Chart Title
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "" 'Left Axis Title
        
        Sheet1.[M1:M3].Copy
        .SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=False, _
            CategoryLabels:=False, Replace:=False, NewSeries:=True
        .SeriesCollection(2).AxisGroup = 2
        .SeriesCollection(2).ChartType = xlLine
        
        .Axes(xlCategory, xlSecondary).HasTitle = False
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "" 'Right Axis Title
    End With
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    ActiveChart.Axes(xlValue, xlSecondary).Select
    With ActiveChart.Axes(xlValue, xlSecondary)
        .MinimumScale = 0
        .MaximumScale = 25
        .MinorUnitIsAuto = True
        .MajorUnit = 5
        .Crosses = xlCustom
        .CrossesAt = 0
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    Selection.TickLabels.NumberFormat = "0"
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = 175
        .MaximumScale = 210
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlCustom
        .CrossesAt = 0
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DreamerZ]

Thanks Skip. Can always count on you. Any idea why it's recorded this way if it doesn't work?



DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[SkipVought]

No.

BTW I recommend Walkenbach's Chart Reference Book.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884