Chartspace & Excel2003

[Ludo]

Hi,

I'm trying to add a chartspace control on a form in Excel2003.
My data is in sheet1 columns A:F with column headers
The whole idea of using this is that it can be dynamical.
I know the work around of John Walkenbach who creates a graph of the
chart to show in a userform, but i'm sure with the new control that it
must be possible to do it without this work around, and will work
faster too.

this is what i found to try, but can't get it working.
I get a Object required error in the verry beginning of the code, so
i'm stuck

Sub BindToSpreadsheet2()
'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.
Dim chConstants
Dim chtChart1

Set chConstants =
frmProfile_configuration.ChartSpace1.Constants
'ChartSpace1.Constants

' Set the data source of ChartSpace1 to Spreadsheet1.
Set ChartSpace1.DataSource =
ThisWorkbook.Sheets("sheet1").Range("A17") '<<<<< Error : Object
required.

' Set a variable to a new chart in Chartspace1.
Set chtChart1 = ChartSpace1.Charts.Add

' Set the chart type.
chtChart1.Type = chConstants.chChartTypeLineMarkers

' Bind the series name to cell B1 in the first sheet of
Spreadsheet1.
chtChart1.SetData chConstants.chDimSeriesNames,
chConstants.chDataBound, "B1"

' Bind the category axis to cell A2:A28 in the first sheet of
Spreadsheet1.
chtChart1.SetData chConstants.chDimCategories,
chConstants.chDataBound, "A2:A28"

' Bind the values of the data series to cells B2:B28 in the first
sheet of Spreadsheet1.
chtChart1.SeriesCollection(0).SetData chConstants.chDimValues,
chConstants.chDataBound, "B2:B28"

End Sub

second attempt, same problem

Sub BindToSpreadsheet()
Dim chConstants
Dim chtChart1
Dim MyMinutes As Range
Dim MyTemperture As Range
Dim MyVibration As Range
Dim MyChartData As Range
Dim MyLastRow As Integer

Range("e1").Select
MyLastRow = Selection.CurrentRegion.Rows.Count
Set chConstants = frmProfile_configuration.ChartSpace1.Constants
Sheets("sheet1").Select
With ActiveWorkbook
Range("A1:A" & MyLastRow).Name = "MyMinutesRange"
'define minutes name
Range("B1:B" & MyLastRow).Name = "MyTempertureRange"
'define temperture name
Range("D1" & MyLastRow).Name = "MyVibrationRange"
'define vibration name
End With
Set MyMinutes = Names("MyminutesRange").RefersToRange
'set MyWeek to the MyWeekRange range
Set MyTemperture =
Names("MyTempertureRange").RefersToRange 'set MyFPY to
the MyDataRange range
Set MyVibration = Names("MyVibrationRange").RefersToRange
Application.Union(MyMinutes, MyTemperture,
MyVibration).Select 'select both ranges to use for
the graph

Set MyChartData = Selection 'Sheets("configure
fmt").Name("MyChartData")
' Set the data source of ChartSpace1 to Spreadsheet1.
Set ChartSpace1.DataSource = MyChartData '<< Object
required

' Set a variable to a new chart in Chartspace1.
Set chtChart1 = ChartSpace1.Charts.Add

' Set the chart type.
chtChart1.Type = chConstants.chChartTypeLineMarkers

' Bind the series name to cell B1 in the first sheet of
Spreadsheet1.
chtChart1.SetData chConstants.chDimSeriesNames,
chConstants.chDataBound, "B1"

' Bind the category axis to cell A2:A28 in the first sheet of
Spreadsheet1.
chtChart1.SetData chConstants.chDimCategories,
chConstants.chDataBound, "A2:A28"

' Bind the values of the data series to cells B2:B28 in the first
sheet of Spreadsheet1.
chtChart1.SeriesCollection(0).SetData chConstants.chDimValues,
chConstants.chDataBound, "B2:B28"

End Sub

Is there anyone out there who have a working example of this control
with Excel2003?

Regards,
Ludo