Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Chart with two axes
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 Whe=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Chart with two axes
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" wrote in message ... 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 Whe=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Chart with two axes
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" wrote: 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" wrote in message ... 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 Whe=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Chart with two axes
Here's how to do it manually:
http://peltiertech.com/Excel/Charts/...OnTwoAxes.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" wrote in message ... 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" wrote: 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" wrote in message ... 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 Whe=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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Chart with two axes
Not sure why you need to comment out
.SeriesCollection(2).AxisGroup = 2 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 Whe=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column-line chart on two axes | Charts and Charting in Excel | |||
Column Chart with 2 axes | Charts and Charting in Excel | |||
Column chart with two axes | Charts and Charting in Excel | |||
Two-Column, Two Axes Chart | Charts and Charting in Excel | |||
I need a customized Excel chart: Column - Column on 2 Axes | Charts and Charting in Excel |