Activechart.SetSourceMethod

  • Thread starter Thread starter grinning_crow
  • Start date Start date
G

grinning_crow

Hi

I'm trying to write a sub-procedure that creates charts based o
variables entered from a userform. Two of the variables specify a dat
range for which the graph should plot. This therefore means that I nee
to use two ranges within the source parameter of the SetSourceDat
method - one for the headings, and one for the data itself, whic
begins a variable number of rows below the headings. As well as this
there are a variable number of column sets which contain similar dat
but for different reporting areas.

i.e. something like this:

Activechart.SetSourceData Sheet1.Range(FromColumn & "2:" & ToColumn
"3" _ 'setting the headings from rows 2 and 3
' and then the second range grabbing the data:
, FromColumn & FromRow & ":" & ToColumn & ToRow), xlColumns

etc.

Unfortunately, when it creates the graph, its not recognising it as tw
distinct ranges, but creating one graph using the entire number of row
in the current region for the specified columns.

I've no doubt I'm being extremely dim and its right in front of me, bu
if someone could point me to a solution, that would be appreciated.

Thanks
 
You have headings here which are series names, two cells per series
name, plus the data below. No Category labels?

If you have category labels, you can set up a discontiguous range for
setsourcedata which will do this neatly. The range is a rectangular
region containing the names, categories, values, and the blank region
(it's gotta be blank) at the top left, where the rows of the names
intersect with the columns of the categories. This macro takes your
starting and ending row locations for the values, names, and categories,
and applies the appropriate range to the chart:

Sub SetMySourceData()
Dim mySheet As Worksheet
Dim myChart As Chart
Dim myRange As Range
Dim myRangeBlank As Range
Dim myRangeNames As Range
Dim myRangeCats As Range
Dim myRangeValues As Range
Dim NameRow1 As Integer
Dim NameRow2 As Integer
Dim CatCol1 As Integer
Dim CatCol2 As Integer
Dim DataRow1 As Integer
Dim DataCol1 As Integer
Dim DataRow2 As Integer
Dim DataCol2 As Integer

'' Dummy range definition points
'' Your code supplies them
NameRow1 = 3
NameRow2 = 4
CatCol1 = 2
CatCol2 = 3
DataRow1 = 6
DataCol1 = 6
DataRow2 = 9
DataCol2 = 8

'' Dummy object definitions
'' Your code supplies them
Set mySheet = ActiveSheet
Set myChart = mySheet.ChartObjects(1).Chart

With mySheet
'' Build the subranges
Set myRangeBlank = .Range(.Cells(NameRow1, CatCol1), _
.Cells(NameRow2, CatCol2))
Set myRangeNames = .Range(.Cells(NameRow1, DataCol1), _
.Cells(NameRow2, DataCol2))
Set myRangeCats = .Range(.Cells(DataRow1, CatCol1), _
.Cells(DataRow2, CatCol2))
Set myRangeValues = .Range(.Cells(DataRow1, DataCol1), _
.Cells(DataRow2, DataCol2))

'' Just checking
Debug.Print myRangeBlank.Address
Debug.Print myRangeNames.Address
Debug.Print myRangeCats.Address
Debug.Print myRangeValues.Address

'' Unify the range
Set myRange = Union(myRangeBlank, myRangeNames, _
myRangeCats, myRangeValues)

End With
myChart.SetSourceData Source:=myRange, PlotBy:=xlColumns

End Sub


- Jon
 
An alternative is to set the data range for each series separately, as
below. If you don't have categories, omit the relevant line.

Sub SetMySeriesData()
Dim mySheet As Worksheet
Dim myChart As Chart
Dim myRange As Range
Dim mySrs As Series
Dim myRangeNames As Range
Dim myRangeCats As Range
Dim myRangeValues As Range
Dim NameRow1 As Integer
Dim NameRow2 As Integer
Dim CatCol1 As Integer
Dim CatCol2 As Integer
Dim DataRow1 As Integer
Dim DataCol1 As Integer
Dim DataRow2 As Integer
Dim DataCol2 As Integer
Dim i As Integer

'' Dummy range definition points
'' Your code supplies them
NameRow1 = 3
NameRow2 = 4
CatCol1 = 2
CatCol2 = 3
DataRow1 = 6
DataCol1 = 6
DataRow2 = 9
DataCol2 = 8

'' Dummy object definitions
'' Your code supplies them
Set mySheet = ActiveSheet
Set myChart = mySheet.ChartObjects(1).Chart

With mySheet
'' Build the subranges
Set myRangeNames = .Range(.Cells(NameRow1, DataCol1), _
.Cells(NameRow2, DataCol2))
Set myRangeCats = .Range(.Cells(DataRow1, CatCol1), _
.Cells(DataRow2, CatCol2))
Set myRangeValues = .Range(.Cells(DataRow1, DataCol1), _
.Cells(DataRow2, DataCol2))

'' Just checking
Debug.Print myRangeNames.Address
Debug.Print myRangeCats.Address
Debug.Print myRangeValues.Address

End With

Do While myChart.SeriesCollection.Count > 0
myChart.SeriesCollection(1).Delete
Loop

For i = 1 To myRangeValues.Columns.Count
Set mySrs = myChart.SeriesCollection.NewSeries
With mySrs
.Values = myRangeValues.Columns(i)
'' OMIT FOLLOWING LINE IF NO CATEGORIES
.XValues = myRangeCats
.Name = "=" & myRangeNames.Columns(i).Address _
(ReferenceStyle:=xlR1C1, external:=True)
End With
Next

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Jon said:
You have headings here which are series names, two cells per series
name, plus the data below. No Category labels?

If you have category labels, you can set up a discontiguous range for
setsourcedata which will do this neatly. The range is a rectangular
region containing the names, categories, values, and the blank region
(it's gotta be blank) at the top left, where the rows of the names
intersect with the columns of the categories. This macro takes your
starting and ending row locations for the values, names, and categories,
and applies the appropriate range to the chart:

Sub SetMySourceData()
Dim mySheet As Worksheet
Dim myChart As Chart
Dim myRange As Range
Dim myRangeBlank As Range
Dim myRangeNames As Range
Dim myRangeCats As Range
Dim myRangeValues As Range
Dim NameRow1 As Integer
Dim NameRow2 As Integer
Dim CatCol1 As Integer
Dim CatCol2 As Integer
Dim DataRow1 As Integer
Dim DataCol1 As Integer
Dim DataRow2 As Integer
Dim DataCol2 As Integer

'' Dummy range definition points
'' Your code supplies them
NameRow1 = 3
NameRow2 = 4
CatCol1 = 2
CatCol2 = 3
DataRow1 = 6
DataCol1 = 6
DataRow2 = 9
DataCol2 = 8

'' Dummy object definitions
'' Your code supplies them
Set mySheet = ActiveSheet
Set myChart = mySheet.ChartObjects(1).Chart

With mySheet
'' Build the subranges
Set myRangeBlank = .Range(.Cells(NameRow1, CatCol1), _
.Cells(NameRow2, CatCol2))
Set myRangeNames = .Range(.Cells(NameRow1, DataCol1), _
.Cells(NameRow2, DataCol2))
Set myRangeCats = .Range(.Cells(DataRow1, CatCol1), _
.Cells(DataRow2, CatCol2))
Set myRangeValues = .Range(.Cells(DataRow1, DataCol1), _
.Cells(DataRow2, DataCol2))

'' Just checking
Debug.Print myRangeBlank.Address
Debug.Print myRangeNames.Address
Debug.Print myRangeCats.Address
Debug.Print myRangeValues.Address

'' Unify the range
Set myRange = Union(myRangeBlank, myRangeNames, _
myRangeCats, myRangeValues)

End With
myChart.SetSourceData Source:=myRange, PlotBy:=xlColumns

End Sub


- Jon
Click to expand...
 
Back
Top