Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chartspace & Excel2003
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("A1:D7") '<<<<< 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:D" & 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chartspace Data Source and Connection String Problem | Excel Programming | |||
Chartspace | Excel Programming | |||
Multiple charts in ChartSpace; problems with double Categories | Charts and Charting in Excel | |||
Multiple charts in ChartSpace; problems with double Categories | Charts and Charting in Excel | |||
Using chartspace in a user form | Excel Programming |