Editing Chart Objects in Code

Can anyone please explain why the first statement marked
OK works but the second doesn't? I've checked that the
sheet names are consistent with the sheets in the actual
workbook. Any offers welcome!

Windows(CurrentWorkbook).Activate
Sheets("Comparative graph").Select
ActiveSheet.Unprotect
If CompGraph = "L" Then
ActiveSheet.ChartObjects("Chart 3").Activate '
2nd Comparative Graph
Else
ActiveSheet.ChartObjects("Chart 4").Activate '
3rd Comparative Graph
End If
(OK)ActiveChart.SeriesCollection(1).Values = _
"='" & CFLsheetName & "'!R6C40:R57C40"
(Unable to set the value)ActiveChart.SeriesCollection
(2).Values = _
"='" & JSsheetName & "'!R6C50:R57C50"
ActiveSheet.Protect

Geoff Goodacre wrote:
I've checked that the
sheet names are consistent with the sheets in the actual
workbook. Any offers welcome!


Hard to tell.
What is JSsheetName?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Hi Bill,

Thanks for your response. JSsheetName and CFLsheetName
are text variables that at the time the code is run
contain the names of the sheets to which the charts are to
be pointed. I've checked that the variables are
consistent (to the last letter, space and capitalisation)
with the sheet names in the workbook.

-----Original Message-----
Geoff Goodacre wrote:
I've checked that the
sheet names are consistent with the sheets in the
actual
workbook. Any offers welcome!


Hard to tell.
What is JSsheetName?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

.

Geoff Goodacre wrote:
JSsheetName and CFLsheetName
are text variables

Yes.
I wanted to know the value of the JSsheetName variable at the time the
statement is executed.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

The value of the variable was "JS Loose Braeburn".
However, I've resolved the problem. I noticed that the
cells referred to in the first series contained values,
and those in the second series were empty (the code is
building a template that will be populated with numbers in
a later process). I keyed zeroes into the cells
referenced and the code now works. The same action
carried out manually does not object to the empty cells.

Sorry if this was a 'red herring' but this 'quirk' of at
least Excel 97 and 2000 doesn't seem to be documented
anywhere!

-----Original Message-----
Geoff Goodacre wrote:
JSsheetName and CFLsheetName
are text variables

Yes.
I wanted to know the value of the JSsheetName variable at
the time the
statement is executed.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

.