More chart problem Excel 97 - unable to set propert of series class

[Karoo News]

Hi can some help this code work I have to problems

Prob 1: is unable to set property of series class for the code on the xv
sv1 - sv3 in the following parts of this code: .SeriesCollection(1).Values
= sv1
Its is OK if the actual code is written as "='Mon 2nd-wk1' !R44C43:R44C32"
but not when used as sv1

Prob 2: the last two lines of code wont work. The graph when place in
C2:AF14 needs to be slighlty wider and the code was suggested on here but
does not work!


Sub ChartTrading()


Set r = Range("C2:AF14")

xv = "='" & ActiveSheet.Name & "' !R43C3:R43C32"
sv1 = "='" & ActiveSheet.Name & "' !R44C3:R44C32"
sv2 = "='" & ActiveSheet.Name & "' !R16C3:R16C32"
sv3 = "='" & ActiveSheet.Name & "' !R51C3:R51C32"

With r
Set chtobj = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width,
..Height)
End With

With chtobj.Chart

.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Mon 2nd-w1").Range("A1")
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = xv
.SeriesCollection(1).Values = sv1
.SeriesCollection(2).Values = sv2
.SeriesCollection(3).Values = sv3
.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.Legend.Delete

End With

ActiveChart.Parent.ShapeRange.ScaleWidth 1.01, msoFalse,
msoScaleFromTopLeft
ActiveChart.Parent.ShapeRange.ScaleWidth 0.97, msoFalse,
msoScaleFromBottomRight
End Sub

Many Thanks again to all who reply!

[Peter T]

Hello again,

Think I recognise some code in this!

Not sure about prob1, suggest you do something like

Debug,? sv1
look in the immediate window, Ctrl-g, and compare with the string that
works.

Prob 2: You are referring to ActiveChart but there isn't one, at least not
the new chtobj.chart unless you activate it in code

Try
chtobj.ShapeRange.ScaleWidth etc

Instead of "scaling" the chart, why not +/- the .height & .width
dimensions a bit, or multiply by a factor, when you create the chart.

Regards,
Peter T

"Karoo News" <[email protected]> wrote in message
news:[email protected]...
> Hi can some help this code work I have to problems
>
> Prob 1: is unable to set property of series class for the code on the xv
> sv1 - sv3 in the following parts of this code:
..SeriesCollection(1).Values
> = sv1
> Its is OK if the actual code is written as "='Mon 2nd-wk1' !R44C43:R44C32"
> but not when used as sv1
>
> Prob 2: the last two lines of code wont work. The graph when place in
> C2:AF14 needs to be slighlty wider and the code was suggested on here but
> does not work!
>
>
> Sub ChartTrading()
>
>
> Set r = Range("C2:AF14")
>
> xv = "='" & ActiveSheet.Name & "' !R43C3:R43C32"
> Debug.= "='" & ActiveSheet.Name & "' !R44C3:R44C32"
> sv2 = "='" & ActiveSheet.Name & "' !R16C3:R16C32"
> sv3 = "='" & ActiveSheet.Name & "' !R51C3:R51C32"
>
> With r
> Set chtobj = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width,
> .Height)
> End With
>
> With chtobj.Chart
>
> .ChartType = xlColumnClustered
> .SetSourceData Source:=Sheets("Mon 2nd-w1").Range("A1")
> .SeriesCollection.NewSeries
> .SeriesCollection.NewSeries
> .SeriesCollection.NewSeries
> .SeriesCollection(1).XValues = xv
> .SeriesCollection(1).Values = sv1
> .SeriesCollection(2).Values = sv2
> .SeriesCollection(3).Values = sv3
> .Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
> .HasTitle = False
> .Axes(xlCategory, xlPrimary).HasTitle = False
> .Axes(xlValue, xlPrimary).HasTitle = False
> .Legend.Delete
>
> End With
>
> ActiveChart.Parent.ShapeRange.ScaleWidth 1.01, msoFalse,
> msoScaleFromTopLeft
> ActiveChart.Parent.ShapeRange.ScaleWidth 0.97, msoFalse,
> msoScaleFromBottomRight
> End Sub
>
> Many Thanks again to all who reply!
>
>
>
>