VBA for charting

[greg7468]

Hi all,
I am very new to VBA.(but trying)

I have the following code to produce a chart.

Sub AddChartObject()
Dim myChtObj As ChartObject
'
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=20, Width:=700, Top:=120, Height:=225)
myChtObj.Chart.SetSourceData
Source:=Sheets("Sheet1").Range("a3:a6"), _
PlotBy:=xlRows
myChtObj.Chart.ChartType = xlColumnStacked

End Sub

I needed the chart to show the data in each column stacked ontop of
each other (just that column) and worked out to put the plotby rows
command in. That worked great for the first series.

I now have the following code to add a new series,

Sub AddNewSeriesAL0100()
With ActiveChart.SeriesCollection.NewSeries
..Values = ActiveSheet.Range("b3:b6")
End With
End Sub

but when I do it does not stack the data ontop of each other in the
chart, it puts them side by side. I have tried putting the plotby rows
command in but it doesn't seem to work for the new series.

Can anyone help me out to be able to stack any new series.

Thanks for any assistance.

 

[Jon Peltier]

You need to repeat this line when you add a second series:

ActiveChart.ChartType = xlColumnStacked

Excel somehow forgets you had a stacked chart and converts to the
default clustered variety.

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

 

[greg7468]

Thanks for the reply Jon,

however even with the extra code inserted it still does not stack them
for the second series.

Any other ideas welcome.

 

[Jon Peltier]

Okay, now I'm looking more carefully at your first procedure. When you
run this procedure, notice the legend. You have created four series, not
just one. What you want to do in your second procedure is extend each
series, not add a new series. This should do it:

Sub ExtendSeriesCollection()
ActiveChart.SeriesCollection.Extend _
Source:=ActiveSheet.Range("B3:B6"), Rowcol:=xlRows
End Sub

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

 

[greg7468]

Thank you Jon,

just what I wanted