Modifying an Excel Chart in VB - DOM question

[Guest]

I've got a VB6 app that modifies a worksheet that a few charts use for their data. When I recreate the worksheet, the series for the charts are all out of whack, and I need to recreate them.

I've been able to find scant information on programmatically modifying chart series information (using VB6). Here's what I know so far:

1) The total # of series in the chart
chtChart.SeriesCollection.Count

2) set the name of the chart in the legend
chtChart.SeriesCollection(i).Name

3) set the range of the series in the chart
chtChart.SeriesCollection(i).values = <some range>

I need to know the following:

1) How do I specify how the data is displayed (i.e. as a vertical bar or as a data point connected by a (roughly) horizontal line)

2) How do I specify the color of the displayed data bar/line

3) How do I specify the pattern of the displayed data line (i.e. It has a triangle on it, it has a circle on it, etc...)

I hope that this makes sense and that this is the correct forum to post this in.

Thanks for any help you can offer,

Paul Willman

 

[Jon Peltier]

Paul -

I assume you have access to Excel. The easiest way to explore the object
model is to do what you want your code to do, manually, with the macro
recorder running. Sometimes the code it gives you isn't very well
optimized; it records every click and every object you select. But at
least you get the syntax and key words. The Object Browser in Excel's
VBE can fill in the gaps.

The type of chart is given by the ChartType property of the Chart class.
For example:

ActiveChart.ChartType = xlColumnClustered

(a vertical bar chart is called a column chart in Excel).

In a line or scatter chart, you can specify the .MarkerStyle of the
Series object:

ActiveChart.SeriesCollection(1).MarkerStyle = xlDiamond

In addition to Excel's tools (macro recorder and object browser), you
could check out some pages on my web site. This is a good place to start:

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

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

 

[Guest]

Thanks Jon,

Great advice and a useful site - I'm up and moving again!

Paul