Use of MS Chart Control in VBA Form

[bob01]

I am trying to use the Chartspace object on a VBA form in Excel 2002, but am unable to find out how to specify the speadsheet data to be used for each series.

I have found out how to add series, and to add titles & legend etc. but can anybody help with the actual data.

[Andy Pope]

Can you post and example of what you have so far?

[bob01]

This is the code that I have so far - it adds a chart to the chartspace on the form along with title and axis labels. It also sets the colour of series(0) in the legend, but no values are displayed.

Sub AddChart()
ChartSpace1.Charts.Add
ChartSpace1.Charts(0).Type = chChartTypeColumnClustered
ChartSpace1.Charts(0).HasTitle = True
ChartSpace1.Charts(0).HasLegend = True
ChartSpace1.Charts(0).Title.Caption = "Test Chart"
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).Legend.Position = chLegendPositionTop
ChartSpace1.Charts(0).SeriesCollection(0).Type = chChartTypeColumnClustered
ChartSpace1.Charts(0).SeriesCollection(0).Interior.Color = vbRed
ChartSpace1.Charts(0).SeriesCollection(0).Type = chChartTypeColumnClustered
ChartSpace1.Charts(0).Axes(1).HasTitle = True
ChartSpace1.Charts(0).Axes(1).Title.Caption = "Time"
ChartSpace1.Charts(0).Axes(1).MajorTickMarks = True
ChartSpace1.Charts(0).Axes(0).HasTitle = True
ChartSpace1.Charts(0).Axes(0).Title.Caption = "Rate"
End Sub

[Andy Pope]

What is ChartSpace1 ?

[bob01]

Chartspace1 is the name of the MS Office 9 Chart object that I added to the form. I have now found how to add values from an array, which I can populate from the speadsheet. I'm not sure if it is possible to reference the data directly to the spreadsheet, but for now I have a solution.

Thanks

[Andy Pope]

The help is fairly helpful

This example creates a chart that Is bound To a spreadsheet. The series name Is In cell B1, the category names are In cells A2:A28, And the values are In cells B2:B28. 
 
Set c = ChartSpace1.Constants 
Set ChartSpace1.DataSource = Spreadsheet1 
ChartSpace1.Charts.Add 
ChartSpace1.Charts(0).Type = c.chChartTypeLineMarkers 
ChartSpace1.Charts(0).SetData c.chDimSeriesNames, 0, "b1" 
ChartSpace1.Charts(0).SetData c.chDimCategories, 0, "a2:a28" 
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, 0, "b2:b28"