dynamic chart doesn't accept variable as worksheet

[Andreas de Bettignies Dutz]

Hi, Thank you for any help with following problem:

I want to create dynamic charts, with a similar chart on each
worksheet. The chart values are based on named formulas as range.
This works well as long as I hard code the worksheet name when I use
the named formula as input for the chart values, but not when I want
to use a variable as sheet name.

The example might be clearer:

Sub addstudy_Click()
Dim NewStudyName As String
' ..... (other stuff)

'the program creates a new worksheet and asks the user for a name of
the worksheet, which is assigend to the
' NewStudyName variable

ActiveSheet.Name = NewStudyName
' ...........(other stuff)
'next is the definition of names for the chart source data:
Worksheets(NewStudyName).Names.Add Name:="CDAvalues", RefersToR1C1:= _
"=OFFSET('Worksheets(NewStudyName)'!R40C2,0,1,1,SUM('Worksheets(NewStudyName)'!R38C1))"
'...........(other stuff)
'now I set up the chart:
Dim workchart As Chart
Dim aNewSeries As Series
Set workchart = Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, _
Name:=Worksheets(NewStudyName).Name
With ActiveChart.SeriesCollection(1)
.Name = "CDAs"
.XValues = Worksheets(NewStudyName).Range("c65:cg65")

' alternatively this works too:
.XValues = "=" & "'" & Worksheets(NewStudyName).Name & "'" &
"!R65C3:R65C59"

' and here comes the problem:
'the VALUES assignement DOESN'T work:

..Values = "=" & "'" & Worksheets(NewStudyName).Name & "'" &
"!CDAvalues"

: if I replace the first part of the value assignement by a hard coded
worksheet reference e.g.:
.Values = "=study1!CDAvalues"
then it works well too.

I also tried other versions (and many more that seem less correct):
.Values = Worksheets(NewStudyName).Range("CDAvalues")
.Values = "='Worksheets(NewStudyName)'!CDAvalues"
.Values = "=Worksheets(NewStudyName)!CDAvalues"
.Values = Worksheets(NewStudyName).Names(1).RefersToRange
.Values = Worksheets(NewStudyName).Names("CDAvalues").RefersToRange
.Values = Names("CDAvalues").RefersToRange
.Values = ActiveSheet.Range("CDAvalues")

I want to use worksheet specific names, with workbook names I run into
similar problems (after trying to use formats like:
.Values = "=" & ThisWorkbook.Name & "!CDAvalues" ).

If you could help me I very much appreciate it. I work with EXCEL
2000.

Thank you, Andreas

 

[Andy Pope]

Hi Andreas,

Maybe you creation of named range CDAvalues is not working as you
expect. Try this instead.

Worksheets(NewStudyName).Names.Add Name:="CDAvalues", RefersToR1C1:= _
"=OFFSET('" & NewStudyName & "'!R40C2,0,1,1,SUM('" & _
NewStudyName & "'!R38C1))"

Cheers
Andy

 

[Andreas de Bettignies Dutz]

Andy,

You were correct, the error was in the named range. Now it works.

Thank you so much! Andreas