TypeName:="Line - Stacked Column on 2 Axes" ???

[Kevin]

The following code works well. I've been asked to show the two columns as
stacked. Is this something that be programmed? Thanks.

Kevin


Sub TestChart()
Dim Series1Rng As Range
Dim Series2Rng As Range
Dim Series3Rng As Range
Dim SeriesXValuesRng As Range
DeleteCharts
strSheetName$ = "Charts"
Set SeriesXValuesRng = Worksheets(strSheetName$).Range("A2:A4")
Set Series1Rng = Worksheets(strSheetName$).Range("B2:B4")
Set Series2Rng = Worksheets(strSheetName$).Range("C2:C4")
Set Series3Rng = Worksheets(strSheetName$).Range("D24")
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=600, Top:=100, Height:=200)
With .Chart.SeriesCollection.NewSeries
.Values = Series1Rng
.XValues = SeriesXValuesRng
End With
With .Chart.SeriesCollection.NewSeries
.Values = Series2Rng
End With
With .Chart.SeriesCollection.NewSeries
.Values = Series3Rng
End With
.Chart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line -
Column on 2 Axes"
.Chart.Location Where:=xlLocationAsObject, Name:=strSheetName$
.Chart.HasLegend = False
End With
Sheets(strSheetName$).Range("E1").Select
End Sub

'Data:
'1 A B C D
'2 Jul-08 2768 1094 144204
'3 Aug-08 2627 1022 153008
'4 Sep-08 5348 1244 197267

 

[Andy Pope]

Hi,

Yes, you just need to change the chart types of the series

Sub TestChart()
Dim strSheetName$
Dim Series1Rng As Range
Dim Series2Rng As Range
Dim Series3Rng As Range
Dim SeriesXValuesRng As Range
' DeleteCharts
strSheetName$ = "Charts"
Set SeriesXValuesRng = Worksheets(strSheetName$).Range("A2:A4")
Set Series1Rng = Worksheets(strSheetName$).Range("B2:B4")
Set Series2Rng = Worksheets(strSheetName$).Range("C2:C4")
Set Series3Rng = Worksheets(strSheetName$).Range("D24")
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=600, Top:=100, Height:=200)
With .Chart.SeriesCollection.NewSeries
.Values = Series1Rng
.XValues = SeriesXValuesRng
.ChartType = xlColumnStacked
End With
With .Chart.SeriesCollection.NewSeries
.Values = Series2Rng
.ChartType = xlColumnStacked
End With
With .Chart.SeriesCollection.NewSeries
.Values = Series3Rng
.ChartType = xlLineMarkers
.AxisGroup = 2
End With
.Chart.HasLegend = False
End With
Sheets(strSheetName$).Range("E1").Select
End Sub

Cheers
Andy

 

[Kevin]

Thanks Andy. Works like a charm.

Hi,

Yes, you just need to change the chart types of the series

Sub TestChart()
Dim strSheetName$
Dim Series1Rng As Range
Dim Series2Rng As Range
Dim Series3Rng As Range
Dim SeriesXValuesRng As Range
' DeleteCharts
strSheetName$ = "Charts"
Set SeriesXValuesRng = Worksheets(strSheetName$).Range("A2:A4")
Set Series1Rng = Worksheets(strSheetName$).Range("B2:B4")
Set Series2Rng = Worksheets(strSheetName$).Range("C2:C4")
Set Series3Rng = Worksheets(strSheetName$).Range("D24")
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=600, Top:=100, Height:=200)
With .Chart.SeriesCollection.NewSeries
.Values = Series1Rng
.XValues = SeriesXValuesRng
.ChartType = xlColumnStacked
End With
With .Chart.SeriesCollection.NewSeries
.Values = Series2Rng
.ChartType = xlColumnStacked
End With
With .Chart.SeriesCollection.NewSeries
.Values = Series3Rng
.ChartType = xlLineMarkers
.AxisGroup = 2
End With
.Chart.HasLegend = False
End With
Sheets(strSheetName$).Range("E1").Select
End Sub

Cheers
Andy