Editing Chart Objects in Code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Geoff Goodacre - 02 Jun 2004 17:19 GMT

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

Reply to this Message

Bill Manville - 02 Jun 2004 17:57 GMT

> 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

Reply to this Message

Geoff Goodacre - 03 Jun 2004 10:00 GMT

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-----
>> I've checked that the
[quoted text clipped - 9 lines]
>
>.

Reply to this Message

Bill Manville - 03 Jun 2004 15:44 GMT

> 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

Reply to this Message

Geoff Goodacre - 04 Jun 2004 10:29 GMT

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-----
>> JSsheetName and CFLsheetName
[quoted text clipped - 9 lines]
>
>.

Reply to this Message