Multiple charts on single chart sheet

[Brian Reilly, MVP]

I would like to place two charts on the same chart sheet via VBA which
I can do.

The problem is in resizing the second chart.

The logic I am using is create a chart sheet with no chart.
Create a chart and place it on the empty chart sheet and size and
place it using .top, .left, .height and .width settings. Works
perfectly.

Now I create the second chart and place it on the same chart sheet. If
I use the same method (different values), the second chart either
sizes incorrectly or disappears all together.

Here's the sample code. I have somethings hard-coded and know better
but I am interested only in the sizing methods to use on multiple
charts on a single page.

Any insights or pointers in the right direction to somewhere that has
examples of this done by VBA?

Brian Reilly, PowerPoint MVP

 

[Andy Pope]

Hi Brian,

You missed of your sample code

This, in xl2003, creates a chartsheet with 2 chart objects. Assuming active
cell is empty when run the chart and chartobjects will be empty but
visible.

Sub x()

Dim chtHolder As Chart
Dim chtTempA As ChartObject
Dim chtTempB As ChartObject

Set chtHolder = Charts.Add
With chtHolder
Set chtTempA = .ChartObjects.Add(1, 1, 10, 10)
Set chtTempB = .ChartObjects.Add(1, 1, 10, 10)
With .ChartArea
chtTempA.Height = .Height
chtTempB.Height = .Height
chtTempA.Width = .Width / 2
chtTempB.Width = chtTempA.Width
chtTempB.Left = chtTempA.Left + chtTempA.Width
End With
End With

End Sub

Cheers
Andy

 

[Jon Peltier]

Hi Brian -

You've left out your code...

I ran the following procedure:

Sub TwoChartsOnAChart()

Dim chtParent As Chart
Dim chtob1 As ChartObject
Dim chtob2 As ChartObject

Set chtParent = Charts.Add
With chtParent
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop

Set chtob1 = .ChartObjects.Add _
(.ChartArea.Width * 0.1, .ChartArea.Height * 0.2, _
.ChartArea.Width * 0.35, .ChartArea.Height * 0.6)
With chtob1.Chart
.SetSourceData Source:=Worksheets(1).Range("Range1")
End With

Set chtob2 = .ChartObjects.Add _
(.ChartArea.Width * 0.55, .ChartArea.Height * 0.2, _
.ChartArea.Width * 0.35, .ChartArea.Height * 0.6)
With chtob2.Chart
.SetSourceData Source:=Worksheets(1).Range("Range2")
End With
End With
End Sub

The first chart was drawn as expected, but the second was too tall and wide,
too far to the right, and too low. I could insert this (inside the With
chtParent block) after creating the two charts:

With .ChartArea
chtob1.Left = .Width * 0.1
chtob1.Width = .Width * 0.35
chtob1.Top = .Height * 0.2
chtob1.Height = .Height * 0.6

chtob2.Left = .Width * 0.55
chtob2.Width = .Width * 0.35
chtob2.Top = .Height * 0.2
chtob2.Height = .Height * 0.6
End With

but I discovered that inserting DoEvents after creating the first chart and
before creating the second makes both charts work as expected, without
having to go back and resize.

Post back if this isn't what you meant.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax
(e-mail address removed)
http://PeltierTech.com/
_______

 

[Brian Reilly, MVP]

Thanks Andy and Jon,
This pints me in the right direction. Sorry about leaving out the
code. Meant to include it.

Brian Reilly, PowerPoint MVP