Multiple charts on one worksheet?

[Jim Kelly]

Hi,
I've seen this subject posted before but could not work out my problem
with the answers to those posts, so I'll start my own
I wrote a VB program that invokes Excel and reads data into it (ADO),
selects the appropriate range, then creates a chart on that worksheet.
I then copy that chart to a new worksheet named "Charts" and then run
through the process a number of times depending on the number of
elements a user has asked for (stock symbols). It does this fine
except it places the charts one on top of the other on the "Charts"
sheet. I've tried using the .Top property, but get errors (I'd give
an example of the code I used, but I have tried every combination of
..ActiveChart.whatever that I could get my hands on), I've used macros
then VBEditor to try and emulate that code, but to no avail, and I've
tried using the .Location property specifying the "Charts" sheet
hoping that it would automatically place the second chart top where
the first chart bottom was. Again, no luck.
Since the charts are of the exact same size, I picture a simple
command that will examine the first chart size and/or location on the
sheet, then place the second chart appropriately right where the
previous chart leaves off.
Any suggestions?
Thank you!
Jim

 

[Tom Ogilvy]

With Worksheets("charts")
.ChartObjects(2).top = .ChartObjects(1).Top + _
.ChartObjects(1).Height + 10
.ChartObjects(2).Left = .ChartObjects(1).Left
End With

 

[Ian Millward]

Jim ,

Its a bug. Look at Article 245089 on the M$ Site. It gives a good
workaround. I posted this problem last week but I've got it working fine
now.

Also, have a look at the answer to my posting by someone calling himself M.
He has another workaround.

Hope this helps,

Ian Millward
Edinburgh

 

[Jon Peltier]

And when constructing the charts, don't use the simple code that the
macro recorder gave you. You can replace all the charts.add ...
activechart.location stuff with:

Dim wsChart as Worksheet ' Holds the charts
Dim wsData as Worksheet ' Holds the data
Dim ChtOb as ChartObject

Set ChtOb = wsChart.ChartObjects.Add(L, T, W, H)
' Length, Top, Width, Height in points

With ChtOb.Chart
' Put all the stuff in here to construct the chart
.SetSourceData Source:=wsData.Range("whatever")
' Also axis and series formatting, etc.
End With

You don't need to select the particular sheets or charts in order for
this to work. See more VBA charting tips here:

http://peltiertech.com/Excel/Charts/chartvba.html

and some good starting code samples here:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon

 

[Jim Kelly]

Thank you all! It's nice to have experts like you to lean on!!

Jim

 

[Jim Kelly]

With Worksheets("charts")
.ChartObjects(2).top = .ChartObjects(1).Top + _
.ChartObjects(1).Height + 10
.ChartObjects(2).Left = .ChartObjects(1).Left
End With

Tom (and all ),
I got home and plugged in your code and got an error "Unable to get
the Chartobjects property of the Worksheets class". I have not been
programming very long, but this sounds like I don't have a proper
reference (?). I have MS Excel 9.0 Object Library and MS Graphs 9.0
Object Library (not even sure that I need that one?) referenced in
VB6. I'm running Excel 2000 on an XP machine.
Do you see any glaring errors?
Thanks,
Jim

 

[Jim Kelly]

With Worksheets("charts")
.ChartObjects(2).top = .ChartObjects(1).Top + _
.ChartObjects(1).Height + 10
.ChartObjects(2).Left = .ChartObjects(1).Left
End With

OK, disregard that last post as I accidently started moving
ChartObjects around before I put the objects on the page! I told you
I was new at this! Sorry for the time wasting, but it brings up a
question: Why do some objects not bring up a list of properties? For
example, in the above code, when you go to type the second line and
you type the period, you expect to get a list of allowable
properties....I don't get that with Excel objects. Is this normal?
Does this even make sense??
Thanks again for your help folks!
Jim

 

[Jim Kelly]

And when constructing the charts, don't use the simple code that the
macro recorder gave you. You can replace all the charts.add ...
activechart.location stuff with:

Dim wsChart as Worksheet ' Holds the charts
Dim wsData as Worksheet ' Holds the data
Dim ChtOb as ChartObject

Set ChtOb = wsChart.ChartObjects.Add(L, T, W, H)
' Length, Top, Width, Height in points

With ChtOb.Chart
' Put all the stuff in here to construct the chart
.SetSourceData Source:=wsData.Range("whatever")
' Also axis and series formatting, etc.
End With

You don't need to select the particular sheets or charts in order for
this to work. See more VBA charting tips here:

http://peltiertech.com/Excel/Charts/chartvba.html

and some good starting code samples here:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------

Jon, I'm enjoying going through your website...learning a ton of
stuff! Thanks for taking the time to make it available to those of us
interested in learning more about Excel!!
And thank you for the tips on cleaner coding above.
Jim