Answered by:
Managing two charts on one chart sheet
Question

In order to clearly show two data sets, I would like to have two charts on the same page. One way to do this is with a trick explained in the Office 2007 Help and HowTo ("Displaying multiple charts on a chart sheet"  basically: make a blank chart page, then move charts onto it using the "Move Chart" function in the Design tab).
My problem is that I am unable to access the charts in Visual Basic. Although Excel sees that there is a chart page with the expected name, the SeriesCollection.Count for that chart is zero, even though the chart contains two charts that each have one series. Perhaps when I moved the two charts to the empty chart page they suddenly became noncharts (or excharts), thereby preventing VB from accessing them. Is this what happened, or is there another way to access the embedded charts?
On a similar theme, if I put two charts onto a normal worksheet, I don't seem to be able to access the charts in a macro, as they aren't Charts and they aren't Sheets.
Thanks in advance for your insights.
Answers

Embedded charts chan be created on a Chart sheet just as in a Worksheet, IOW a chart on a chart. Manually a little bit of "trickery" is involved, but with code none at all as the method is the same. Try this in a new workbook 
[bvbnet]
Sub test()
Dim i As Long
Dim L As Double, T As Double, W As Double, H As Double
Dim chtSht As Chart
Dim chtObj As ChartObject, cht As Chart
Dim sr As Series, ax As Axis
Dim rng As Range
Set rng = ActiveWorkbook.Worksheets(1).Range("A1:D4")
rng.Formula = "=int(rand()*10)"
Set chtSht = ActiveWorkbook.Charts.Add
chtSht.ChartObjects.Delete
W = chtSht.ChartArea.Width / 2
H = chtSht.ChartArea.Height / 2
For i = 1 To 4
L = (i  1) Mod 2
L = W * L
If i >= 3 Then
T = H
End If
Set chtObj = chtSht.ChartObjects.Add(L, T, W, H)
Set cht = chtObj.Chart
Set sr = cht.SeriesCollection.NewSeries
sr.Values = rng.Columns(i)
sr.Name = "data " & i
cht.HasTitle = True
cht.ChartTitle.Text = chtObj.Name
Set ax = cht.Axes(xlValue)
ax.MaximumScale = 10
Next
MsgBox "PRESS F9 TO RECALC"
End Sub
[/vbnet]To rereference the embedded charts, fully reference like this
ChartSheet, ChartObject, Chart
egSet chtSht = ActiveWorkbook.Charts("Chart 1")
Set chtObj = chtSht.ChartObjects("Chart 1")
or by index
Set chtObj = chtSht.ChartObjects(2)
Set cht = chtObj.Chartnow work with the "cht" etc
Peter Thornton
 Marked as answer by mr510 Tuesday, August 2, 2011 12:03 AM
All replies

You can definitely work with two charts one a worksheet, I've done it many times. Try recording some code of you formatting both charts and you'll get what you need.
Make sure you also have SP2 installed for Office 2007.
Rod Gill
The one and only Project VBA Book Rod Gill Project Management 
Embedded charts chan be created on a Chart sheet just as in a Worksheet, IOW a chart on a chart. Manually a little bit of "trickery" is involved, but with code none at all as the method is the same. Try this in a new workbook 
[bvbnet]
Sub test()
Dim i As Long
Dim L As Double, T As Double, W As Double, H As Double
Dim chtSht As Chart
Dim chtObj As ChartObject, cht As Chart
Dim sr As Series, ax As Axis
Dim rng As Range
Set rng = ActiveWorkbook.Worksheets(1).Range("A1:D4")
rng.Formula = "=int(rand()*10)"
Set chtSht = ActiveWorkbook.Charts.Add
chtSht.ChartObjects.Delete
W = chtSht.ChartArea.Width / 2
H = chtSht.ChartArea.Height / 2
For i = 1 To 4
L = (i  1) Mod 2
L = W * L
If i >= 3 Then
T = H
End If
Set chtObj = chtSht.ChartObjects.Add(L, T, W, H)
Set cht = chtObj.Chart
Set sr = cht.SeriesCollection.NewSeries
sr.Values = rng.Columns(i)
sr.Name = "data " & i
cht.HasTitle = True
cht.ChartTitle.Text = chtObj.Name
Set ax = cht.Axes(xlValue)
ax.MaximumScale = 10
Next
MsgBox "PRESS F9 TO RECALC"
End Sub
[/vbnet]To rereference the embedded charts, fully reference like this
ChartSheet, ChartObject, Chart
egSet chtSht = ActiveWorkbook.Charts("Chart 1")
Set chtObj = chtSht.ChartObjects("Chart 1")
or by index
Set chtObj = chtSht.ChartObjects(2)
Set cht = chtObj.Chartnow work with the "cht" etc
Peter Thornton
 Marked as answer by mr510 Tuesday, August 2, 2011 12:03 AM

Thanks for the quick response and VB code. After some tweaks and struggle, I got something working.
I ran across a few quirks (IMHO), such as if you add a new chart with ActiveWorkbook.Charts.Add while a data range is in the clipboard (e.g., from a previous copy opeation), the chart won't be blank, but will have the clipboard contents as the data. Also, some grumpiness from Excel with chtSht.ChartObjects.Delete, working sometimes, not working sometimes unless reordered.
But all in all a success.
FWIW, this is the functional but not ideal code that I ended up with (note that the "Temperature" sheet already has a chart on it:
Sheets("Temperature").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range("'Data'!$c$6:$C$1200,'Data'!$f$6:$f$1200")
With ActiveChart.ChartArea
.Height = 150
.Width = 350
.Top = 0
.Left = 330
End With' do some formatting of the X and Y axes
Set ax = ActiveChart.Axes(xlValue)
With ax
.HasMajorGridlines = False
.HasMinorGridlines = False
.HasTitle = True
.AxisTitle.Font.Size = 12
.MinimumScale = 300'...
End With
Set ax = ActiveChart.Axes(xlCategory)
With ax
.HasMajorGridlines = False
.HasMinorGridlines = False
.HasTitle = False
.TickLabels.Font.Name = "Arial"
.TickLabels.NumberFormat = "mm/dd"
.TickLabels.Font.Size = 10
' ...End With