Line-column chart: failing macros

grautu

New Member
Joined
Sep 26, 2004
Messages
38
Hi!
I encounter problems in obtaining a macro that inserts a "Line-column on 2 Axes" chart. The manual procedure always works and produces nice graphs but the code obtained by macro recorder always fails to work. After several attempts and variants I could say that implementing the secondary graph is the problem.
Here is the code (one of several variants which I tried):
Code:
Sub graph()
'
    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"
    ActiveChart.SetSourceData Source:=Sheets("MY_SHEET").Range("A1:C238"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="MY_SHEET"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "GRAPH"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "TIME"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "VOLUME"
        .Axes(xlCategory, xlSecondary).HasTitle = True      '!!!HERE IS THE POINT!!!
        .Axes(xlCategory, xlSecondary).AxisTitle.Characters.Text = "TIME"
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "PRICE"
    End With
End Sub
REMARKS:
------------
Whenever trying to run the macro, one obtains the following message:
Code:
Run-time error '1004'
Method 'Axes' of object '_Chart' failed
and the code line which throws that message is
Code:
.Axes(xlCategory, xlSecondary).HasTitle = True
Any other variant (including "False" choices instead of "True") yields identical effect at the same line, that is the first code line which concerns the secondary graph.
If asking for help (in the debug assistant form) then the systems opens an empty help form and then hangs until closing that empty form.
I've googled on this matter and discovered few similar examples but no one of them appeared to be solved meanwhile.
Could anybody please help me?
Grateful thanks in advance for any opinion!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Andy Pope

Active Member
Joined
Dec 31, 2003
Messages
349
The problem is caused by the 2nd series not actually being on the secondary axis. This a result of the recorder code not being exact enough so that when repeated you get different results.

There are at least 3 ways to solve the problem.

1 - select the data range before the adding the chart
2 - re setting the chart type once the data has been set
3 - explicitly setting the series to the secondary axis

note only 1 of the options is needed.

Code:
Sub graph()
'
    ' 1 - select data range
    Sheets("MY_SHEET").Range("A1:C238").Select

    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"
        
    ActiveChart.SetSourceData Source:=Sheets("MY_SHEET").Range("A1:C238"), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="MY_SHEET"
    With ActiveChart

' 2 re set chart type
        .ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"
        .HasTitle = True
        .ChartTitle.Characters.Text = "GRAPH"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "TIME"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "VOLUME"

' 3 set series to secondary axis        
'        .SeriesCollection(2).AxisGroup = 2
        
        .Axes(xlCategory, xlSecondary).HasTitle = True      '!!!HERE IS THE POINT!!!
        .Axes(xlCategory, xlSecondary).AxisTitle.Characters.Text = "TIME"
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "PRICE"
    End With
End Sub
 

grautu

New Member
Joined
Sep 26, 2004
Messages
38
SOLVED THANKS TO ANDY POPE

Great !!!
I've just tested your first solution for the prescribed range A1:C238 and it works very well. Now I may go on to edit the actual macro that should work with variable parameters (range, sheet name etc).
Thanks a lot !
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top