Column Chart with two axes

G

Gleam

I would like a macro to create a chart with twin columns but different axes.
I have recorded a macro using "Line - Column on 2 Axes" which is the closest
towhat Iwant but when I run it, the code fails. (Excel 2003)
Here is the code with a comment at the point where it falls over:

Sub ChartTest()
Dim sel As Range, sel2 As Range

[a1] = "Causes"
[b1] = "Roll Changes"
[c1] = "Strip Breaks"
[d1] = "Cobbles"

[a2] = "Time Lost"
[b2] = 220
[c2] = 64
[d2] = 5

[a3] = "Occurences"
[b3] = 12
[c3] = 4
[d3] = 1

Columns("A:D").EntireColumn.AutoFit

Set sel = Range("A1:D2")
Set sel2 = Range("A3:D3")

' had to add Activeworkbook to next line
ActiveWorkbook.Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D3"),
PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Delay Causes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time Lost
(min)"
' fails on next line
.Axes(xlCategory, xlSecondary).HasTitle = False ' fails here
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Occurences"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = .MaximumScale * 2
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub
 
P

Peter T

Your code is looks pretty much as I get from the macro recorder. Yet I get
intermittent failures with your code, in particular applying the 2-axis
custom chart type..

Swapping the order of these lines seems to make it more reliable, at least
in my light testing

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D3"), _
PlotBy:=xlRows
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

You don't need to rely on custom charts from the user gallery, set each
series to whichever axis and whatever chart type, eg

.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(2).ChartType = xlLineMarkers

but don't try changing any properties of the secondary axis until at least
one series is on that axis.

You might also look into adding a chartobject to the sheet to size and
position, eg

Sub test()
Dim cht As Chart

Set cht = ActiveSheet.ChartObjects.Add(50, 50, 300, 200).Chart
With cht
..SetSourceData Range("A1:D3"), xlRows
..SeriesCollection(2).AxisGroup = 2
..SeriesCollection(2).ChartType = xlLineMarkers
' more stuff with cht & axes
End With

End Sub

Regards
Peter T

Gleam said:
I would like a macro to create a chart with twin columns but different axes.
I have recorded a macro using "Line - Column on 2 Axes" which is the closest
towhat Iwant but when I run it, the code fails. (Excel 2003)
Here is the code with a comment at the point where it falls over:

Sub ChartTest()
Dim sel As Range, sel2 As Range

[a1] = "Causes"
[b1] = "Roll Changes"
[c1] = "Strip Breaks"
[d1] = "Cobbles"

[a2] = "Time Lost"
[b2] = 220
[c2] = 64
[d2] = 5

[a3] = "Occurences"
[b3] = 12
[c3] = 4
[d3] = 1

Columns("A:D").EntireColumn.AutoFit

Set sel = Range("A1:D2")
Set sel2 = Range("A3:D3")

' had to add Activeworkbook to next line
ActiveWorkbook.Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D3"),
PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Delay Causes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time Lost
(min)"
' fails on next line
.Axes(xlCategory, xlSecondary).HasTitle = False ' fails here
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Occurences"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = .MaximumScale * 2
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub
Click to expand...
 
G

Gleam

Thank you for this. It is now running. I had to comment out the line

.SeriesCollection(2).AxisGroup = 2

When I set the line type to xlColumnClustered for series 2, the columns for
series 2 appear on top of the columns for series 1.
Is there a way to get them side by side?

Peter T said:
Your code is looks pretty much as I get from the macro recorder. Yet I get
intermittent failures with your code, in particular applying the 2-axis
custom chart type..

Swapping the order of these lines seems to make it more reliable, at least
in my light testing

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D3"), _
PlotBy:=xlRows
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

You don't need to rely on custom charts from the user gallery, set each
series to whichever axis and whatever chart type, eg

.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(2).ChartType = xlLineMarkers

but don't try changing any properties of the secondary axis until at least
one series is on that axis.

You might also look into adding a chartobject to the sheet to size and
position, eg

Sub test()
Dim cht As Chart

Set cht = ActiveSheet.ChartObjects.Add(50, 50, 300, 200).Chart
With cht
..SetSourceData Range("A1:D3"), xlRows
..SeriesCollection(2).AxisGroup = 2
..SeriesCollection(2).ChartType = xlLineMarkers
' more stuff with cht & axes
End With

End Sub

Regards
Peter T

Gleam said:
I would like a macro to create a chart with twin columns but different axes.
I have recorded a macro using "Line - Column on 2 Axes" which is the closest
towhat Iwant but when I run it, the code fails. (Excel 2003)
Here is the code with a comment at the point where it falls over:

Sub ChartTest()
Dim sel As Range, sel2 As Range

[a1] = "Causes"
[b1] = "Roll Changes"
[c1] = "Strip Breaks"
[d1] = "Cobbles"

[a2] = "Time Lost"
[b2] = 220
[c2] = 64
[d2] = 5

[a3] = "Occurences"
[b3] = 12
[c3] = 4
[d3] = 1

Columns("A:D").EntireColumn.AutoFit

Set sel = Range("A1:D2")
Set sel2 = Range("A3:D3")

' had to add Activeworkbook to next line
ActiveWorkbook.Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D3"),
PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Delay Causes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time Lost
(min)"
' fails on next line
.Axes(xlCategory, xlSecondary).HasTitle = False ' fails here
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Occurences"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = .MaximumScale * 2
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub
Click to expand...
Click to expand...
 
J

Jon Peltier

Here's how to do it manually:

http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html

Follow the protocol with the macro recorder on while doing this manually,
and merge the recorded code with your existing procedure.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Gleam said:
Thank you for this. It is now running. I had to comment out the line

.SeriesCollection(2).AxisGroup = 2

When I set the line type to xlColumnClustered for series 2, the columns
for
series 2 appear on top of the columns for series 1.
Is there a way to get them side by side?

Peter T said:
Your code is looks pretty much as I get from the macro recorder. Yet I
get
intermittent failures with your code, in particular applying the 2-axis
custom chart type..

Swapping the order of these lines seems to make it more reliable, at
least
in my light testing

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D3"), _
PlotBy:=xlRows
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

You don't need to rely on custom charts from the user gallery, set each
series to whichever axis and whatever chart type, eg

.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(2).ChartType = xlLineMarkers

but don't try changing any properties of the secondary axis until at
least
one series is on that axis.

You might also look into adding a chartobject to the sheet to size and
position, eg

Sub test()
Dim cht As Chart

Set cht = ActiveSheet.ChartObjects.Add(50, 50, 300, 200).Chart
With cht
..SetSourceData Range("A1:D3"), xlRows
..SeriesCollection(2).AxisGroup = 2
..SeriesCollection(2).ChartType = xlLineMarkers
' more stuff with cht & axes
End With

End Sub

Regards
Peter T

Gleam said:
I would like a macro to create a chart with twin columns but different axes.
I have recorded a macro using "Line - Column on 2 Axes" which is the closest
towhat Iwant but when I run it, the code fails. (Excel 2003)
Here is the code with a comment at the point where it falls over:

Sub ChartTest()
Dim sel As Range, sel2 As Range

[a1] = "Causes"
[b1] = "Roll Changes"
[c1] = "Strip Breaks"
[d1] = "Cobbles"

[a2] = "Time Lost"
[b2] = 220
[c2] = 64
[d2] = 5

[a3] = "Occurences"
[b3] = 12
[c3] = 4
[d3] = 1

Columns("A:D").EntireColumn.AutoFit

Set sel = Range("A1:D2")
Set sel2 = Range("A3:D3")

' had to add Activeworkbook to next line
ActiveWorkbook.Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D3"),
PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Delay Causes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time
Lost
(min)"
' fails on next line
.Axes(xlCategory, xlSecondary).HasTitle = False ' fails here
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Occurences"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = .MaximumScale * 2
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 
P

Peter T

Not sure why you need to comment out
.SeriesCollection(2).AxisGroup = 2
Click to expand...

A vba demo just for fun, alternating columns and stacked-columns on the
second axis. Not the best way of arranging the data, just for illustration.

Sub MakeSource()
Dim vArr
Range("B2:E5") = "dummy" 'normally blank or zero
Range("B1,D1") = "Ripe": Range("C1,E1") = "Rotten"

vArr = Array("Apples", "%", "Pears", "%")
Range("A2:A5") = Application.Transpose(vArr)

Range("B2:C2,B4:C4").Formula = "=INT(RAND()*100)"
Range("D3:E3,D5:E5").FormulaR1C1 = "=R[-1]C[-2]"

End Sub

Sub DualChart()
Dim i As Long
Dim cht As Chart
Dim sr As Series

MakeSource

With Range("b7")
Set cht = ActiveSheet.ChartObjects.Add(.Left, .Top, 300, 200).Chart
End With

cht.SetSourceData Range("A1:E5"), xlColumns

For i = 3 To 4
With cht.SeriesCollection(i)
.AxisGroup = xlSecondary
.ChartType = xlColumnStacked100
.Interior.Color = cht.SeriesCollection(i - 2).Interior.Color
End With
Next

cht.HasLegend = True
For i = 4 To 3 Step -1
cht.Legend.LegendEntries(i).Delete
Next
End Sub

Press F9

Regards,
Peter T

Gleam said:
Thank you for this. It is now running. I had to comment out the line

.SeriesCollection(2).AxisGroup = 2

When I set the line type to xlColumnClustered for series 2, the columns for
series 2 appear on top of the columns for series 1.
Is there a way to get them side by side?

Peter T said:
Your code is looks pretty much as I get from the macro recorder. Yet I get
intermittent failures with your code, in particular applying the 2-axis
custom chart type..

Swapping the order of these lines seems to make it more reliable, at least
in my light testing

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D3"), _
PlotBy:=xlRows
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"

You don't need to rely on custom charts from the user gallery, set each
series to whichever axis and whatever chart type, eg

.SeriesCollection(2).AxisGroup = 2
.SeriesCollection(2).ChartType = xlLineMarkers

but don't try changing any properties of the secondary axis until at least
one series is on that axis.

You might also look into adding a chartobject to the sheet to size and
position, eg

Sub test()
Dim cht As Chart

Set cht = ActiveSheet.ChartObjects.Add(50, 50, 300, 200).Chart
With cht
..SetSourceData Range("A1:D3"), xlRows
..SeriesCollection(2).AxisGroup = 2
..SeriesCollection(2).ChartType = xlLineMarkers
' more stuff with cht & axes
End With

End Sub

Regards
Peter T

Gleam said:
I would like a macro to create a chart with twin columns but different axes.
I have recorded a macro using "Line - Column on 2 Axes" which is the closest
towhat Iwant but when I run it, the code fails. (Excel 2003)
Here is the code with a comment at the point where it falls over:

Sub ChartTest()
Dim sel As Range, sel2 As Range

[a1] = "Causes"
[b1] = "Roll Changes"
[c1] = "Strip Breaks"
[d1] = "Cobbles"

[a2] = "Time Lost"
[b2] = 220
[c2] = 64
[d2] = 5

[a3] = "Occurences"
[b3] = 12
[c3] = 4
[d3] = 1

Columns("A:D").EntireColumn.AutoFit

Set sel = Range("A1:D2")
Set sel2 = Range("A3:D3")

' had to add Activeworkbook to next line
ActiveWorkbook.Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D3"),
PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, name:="Sheet1"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Delay Causes"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Time Lost
(min)"
' fails on next line
.Axes(xlCategory, xlSecondary).HasTitle = False ' fails here
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Occurences"
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = .MaximumScale * 2
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Lines on 2 Axes" fails on Excel 2007 1
reverse axis not working as before... 0
Date values not appearing correclty in the x-axis 1
Chart error 2
Setting the colour of the legends in chart. 0
add a chart in a Add-In 1
Compile error in 2003 3
Putting the series 1 and series 2 titles from a worksheet 0

Top