Chart Experts... I have a number of embeded charts inside a charsheet that I need to get a referenc

  • Thread starter news.verizon.net
  • Start date
N

news.verizon.net

Hi,
I can make a chartsheet containing 9 or more embeded charts in one sheet.
And I can move them around using a reference to a "Shapes" object. Can
someone please give me code which will show how I can reference the chart
inside my loop. I'm trying to set the chart series data, or references (in
a scatter plot) and then I also want to format the chart details ( ea. axis
scale, etc).
Thanks very much in advance.
BillReese

=======================================================================================================
Dim xRow As Long, yCol As Long
Dim Cht As Chart, sc as SeriesCollection
Set Cht = .Sheets.Add(before:=.Sheets(1), Type:=xlChart)
Cht.ChartArea.Clear
For xRow = 0 To 2
For yCol = 0 To 2
Set Cht = Cht.ChartObjects.Add(1, 1, 60, 60)
Cht.Name = xRow & yCol
Cht.Shapes(xRow & yCol).Left = ((yCol +
1) * 100) + 10
Cht.Shapes(xRow & yCol).Top = ((xRow +
1) * 100) + 10

'*************** CODE BELOW DOES NOT DO
ANYTHING
Cht.PlotArea.Fill.OneColorGradient
msoGradientHorizontal, Variant:=1, degree:=1
Set sc = Cht.SeriesCollection
sc.Add ""
With sc
.YValues = "={1,2,3,4}"
.XValues = "={10,20,30,40}"
End With
'*************************************************
Next
Next
 
P

Peter T

Many changes to your example -

Sub test()
Dim xRow As Long, yCol As Long
Dim Cht As Chart ', sc As SeriesCollection
Dim chtobj As ChartObject, sr As Series

With ActiveWorkbook
Set Cht = .Sheets.Add(before:=.Sheets(1), _
Type:=xlChart)
End With

'Cht.ChartArea.Clear
For xRow = 0 To 2
For yCol = 0 To 2
'apply entire position & size when adding the chartobject
Set chtobj = Cht.ChartObjects.Add(((yCol + 1) * 100) + 10, _
((xRow + 1) * 100) + 10, 60, 60)
chtobj.Name = xRow & yCol

' Cht.Shapes(xRow & yCol).Left = ((yCol + 1) * 100) + 10
' Cht.Shapes(xRow & yCol).Top = ((xRow + 1) * 100) + 10
' above works but if need to change following simpler
' chtobj.Left = ((yCol + 1) * 100) + 10
' chtobj.Top = ((xRow + 1) * 100) + 10

'there's no plotarea yet, only chartarea
With chtobj.Chart.ChartArea.Fill
.OneColorGradient msoGradientHorizontal, _
Variant:=1, degree:=1
.ForeColor.SchemeColor = 33
.BackColor.SchemeColor = 35
End With

Set sr = chtobj.Chart.SeriesCollection.NewSeries

With sr
.Values = "={1,2,3,4}"
.XValues = "={10,20,30,40}"
End With

'could format chtobj.Chart.plotarea now
'*************************************************
Next
Next
End Sub

Regards,
Peter T
 
Top