Asp Forum - Chart Code - microsoft.public.excel.programming

keri

12/15/2006 9:48:00 AM

Hi I am getting frustrated with the code for the chart as below.
I want the chart series data source to change depending on a checkbox
linked to a cell. However I seem to have a problem with the line that
changes the values. (I recorded the original code from a macro). I have
tried naming the series collection by number but this doesn't work
either. When I step through the code and get to either of the lines
that say " ......values = "=R" then I get a run time error 1004
application or user defined error or a run time error 438 object does
not support this property or method.

Sheets("chart").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select

If ActiveSheet.Range("a3") = True Then
ActiveChart.seriescollection("HAMILTON").Select
ActiveChart.seriescollection("HAMILTON").values = "=R28C4:R34C4"
Else: ActiveChart.seriescollection("HAMILTON").values =
"=R2C2:R20C2"

I'd appreciate any advice anyone can give me as to where I am going
wrong. Thanks.

5 Answers

bda75

12/15/2006 11:17:00 AM

Rather than using code, you could use a dynamic range.

Create a named range with a formula that returns the range depending on
the check box value:
=INDIRECT(IF(checkbox,"rng_a","rng_b"))

where
- "checkbox" is the cell that contains either TRUE or FALSE depending
on the
- rng_a is the range name for range R28C4:R34C4
- rng_b is the range name for range R2C2:R20C2

then use this range name in the definition of the data for your chart.

For a good introduction to using dynamic named ranges:
http://www.ozgrid.com/Excel/Dynamic...
http://www.glencoe.com/ps/computered/pas/article.php4?art...

keri a ?crit :

> Hi I am getting frustrated with the code for the chart as below.
> I want the chart series data source to change depending on a checkbox
> linked to a cell. However I seem to have a problem with the line that
> changes the values. (I recorded the original code from a macro). I have
> tried naming the series collection by number but this doesn't work
> either. When I step through the code and get to either of the lines
> that say " ......values = "=R" then I get a run time error 1004
> application or user defined error or a run time error 438 object does
> not support this property or method.
>
>
> Sheets("chart").Select
> ActiveSheet.ChartObjects("Chart 1").Activate
> ActiveChart.ChartArea.Select
>
> If ActiveSheet.Range("a3") = True Then
> ActiveChart.seriescollection("HAMILTON").Select
> ActiveChart.seriescollection("HAMILTON").values = "=R28C4:R34C4"
> Else: ActiveChart.seriescollection("HAMILTON").values =
> "=R2C2:R20C2"
>
> I'd appreciate any advice anyone can give me as to where I am going
> wrong. Thanks.

Peter T

12/15/2006 11:34:00 AM

You can only apply a source range to the entire chart with SetSourceData. To
link cells to individual series the only way AFAIK is by parsing and writing
the reference in the Series formula, for y-values in the third argument.
Typically, though not necessarily, that'd be between the 2nd & 3rd commas.
Then replace and insert -

rng.Address(, , , True))

If you want to apply 'hard' values (not linked to cells) you could probably
do -
mySeries.values = Evaluate(rng.Address)

There's an absolute max 255 characters limit and including an extra
allowance of 1 per value.

A different approach might be to use dynamic names which perhaps wouldn't
involve any code at all. This could be set up with different offsets from an
anchor cell depending on the value of the checkbox's linked cell.

Regards,
Peter T

"keri" <keri.dowson@diageo.com> wrote in message
news:1166176100.373885.245260@16g2000cwy.googlegroups.com...
> Hi I am getting frustrated with the code for the chart as below.
> I want the chart series data source to change depending on a checkbox
> linked to a cell. However I seem to have a problem with the line that
> changes the values. (I recorded the original code from a macro). I have
> tried naming the series collection by number but this doesn't work
> either. When I step through the code and get to either of the lines
> that say " ......values = "=R" then I get a run time error 1004
> application or user defined error or a run time error 438 object does
> not support this property or method.
>
>
> Sheets("chart").Select
> ActiveSheet.ChartObjects("Chart 1").Activate
> ActiveChart.ChartArea.Select
>
> If ActiveSheet.Range("a3") = True Then
> ActiveChart.seriescollection("HAMILTON").Select
> ActiveChart.seriescollection("HAMILTON").values = "=R28C4:R34C4"
> Else: ActiveChart.seriescollection("HAMILTON").values =
> "=R2C2:R20C2"
>
> I'd appreciate any advice anyone can give me as to where I am going
> wrong. Thanks.
>

Martin Fishlock

12/15/2006 12:03:00 PM

Keri,

The following changes a chart called 'Chart 1' and with the click of a check
box you can show or hide a series line.

There is also the option for dealing with the legend.

If you have 26 series on the chart then you just have 26 check boxes and run
the code.

If you have radio buttons instead and just want to display one series then
you will have to manage the switch over by using a global variable to remeber
which one is showing.

Option Explicit
'legend must be the same number as the series
Private Sub SwitchSeriesLine(seriesLine As Integer, bShowIt As Boolean,
bHaveLegend)
Dim linestyle As XlLineStyle
Dim linecolor As XlColorIndex
Dim fontcolor As XlColorIndex
Dim fontbckgrd As Long

If bShowIt Then
linestyle = xlContinuous
linecolor = xlAutomatic
fontcolor = xlAutomatic
fontbckgrd = xlBackgroundAutomatic
Else
linestyle = xlNone
linecolor = xlNone
fontcolor = 2
fontbckgrd = xlBackgroundTransparent
End If
With Worksheets(1).ChartObjects("Chart 1").chart
With .SeriesCollection(seriesLine)
.Border.linestyle = linestyle
.MarkerBackgroundColorIndex = linecolor
.MarkerForegroundColorIndex = linecolor
End With
If bHaveLegend Then
With .Legend.LegendEntries(seriesLine).Font
.ColorIndex = fontcolor
.Background = fontbckgrd
End With
End If
End With

End Sub

Private Sub CheckBox1_Click()
SwitchSeriesLine 1, Me.CheckBox1.Value, True
End Sub

Private Sub CheckBox2_Click()
SwitchSeriesLine 2, Me.CheckBox2.Value, True
End Sub

Private Sub CheckBox3_Click()
SwitchSeriesLine 3, Me.CheckBox3.Value, True
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.

"keri" wrote:

> Hi I am getting frustrated with the code for the chart as below.
> I want the chart series data source to change depending on a checkbox
> linked to a cell. However I seem to have a problem with the line that
> changes the values. (I recorded the original code from a macro). I have
> tried naming the series collection by number but this doesn't work
> either. When I step through the code and get to either of the lines
> that say " ......values = "=R" then I get a run time error 1004
> application or user defined error or a run time error 438 object does
> not support this property or method.
>
>
> Sheets("chart").Select
> ActiveSheet.ChartObjects("Chart 1").Activate
> ActiveChart.ChartArea.Select
>
> If ActiveSheet.Range("a3") = True Then
> ActiveChart.seriescollection("HAMILTON").Select
> ActiveChart.seriescollection("HAMILTON").values = "=R28C4:R34C4"
> Else: ActiveChart.seriescollection("HAMILTON").values =
> "=R2C2:R20C2"
>
> I'd appreciate any advice anyone can give me as to where I am going
> wrong. Thanks.
>
>

Jon Peltier

12/15/2006 2:19:00 PM

You could use dynamic ranges, as others have suggested, or you could
properly reference the ranges for the .Values property of the series by
including the sheet name.

ActiveChart.seriescollection("HAMILTON").values = "=Sheet1!R28C4:R34C4"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://Pelti...
_______

"keri" <keri.dowson@diageo.com> wrote in message
news:1166176100.373885.245260@16g2000cwy.googlegroups.com...
> Hi I am getting frustrated with the code for the chart as below.
> I want the chart series data source to change depending on a checkbox
> linked to a cell. However I seem to have a problem with the line that
> changes the values. (I recorded the original code from a macro). I have
> tried naming the series collection by number but this doesn't work
> either. When I step through the code and get to either of the lines
> that say " ......values = "=R" then I get a run time error 1004
> application or user defined error or a run time error 438 object does
> not support this property or method.
>
>
> Sheets("chart").Select
> ActiveSheet.ChartObjects("Chart 1").Activate
> ActiveChart.ChartArea.Select
>
> If ActiveSheet.Range("a3") = True Then
> ActiveChart.seriescollection("HAMILTON").Select
> ActiveChart.seriescollection("HAMILTON").values = "=R28C4:R34C4"
> Else: ActiveChart.seriescollection("HAMILTON").values =
> "=R2C2:R20C2"
>
> I'd appreciate any advice anyone can give me as to where I am going
> wrong. Thanks.
>

Jon Peltier

12/15/2006 2:21:00 PM

Or you could simply use the value of the option buttons' linked cell in the
definition of the dynamic range, and not use any of this code.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://Pelti...
_______

"Martin Fishlock" <martin_fishlock@yahoo.co.uk.cutthis> wrote in message
news:E28CD7A3-07AC-41A2-8C29-DAEA91362972@microsoft.com...
> Keri,
>
> The following changes a chart called 'Chart 1' and with the click of a
> check
> box you can show or hide a series line.
>
> There is also the option for dealing with the legend.
>
> If you have 26 series on the chart then you just have 26 check boxes and
> run
> the code.
>
> If you have radio buttons instead and just want to display one series then
> you will have to manage the switch over by using a global variable to
> remeber
> which one is showing.
>
>
>
> Option Explicit
> 'legend must be the same number as the series
> Private Sub SwitchSeriesLine(seriesLine As Integer, bShowIt As Boolean,
> bHaveLegend)
> Dim linestyle As XlLineStyle
> Dim linecolor As XlColorIndex
> Dim fontcolor As XlColorIndex
> Dim fontbckgrd As Long
>
> If bShowIt Then
> linestyle = xlContinuous
> linecolor = xlAutomatic
> fontcolor = xlAutomatic
> fontbckgrd = xlBackgroundAutomatic
> Else
> linestyle = xlNone
> linecolor = xlNone
> fontcolor = 2
> fontbckgrd = xlBackgroundTransparent
> End If
> With Worksheets(1).ChartObjects("Chart 1").chart
> With .SeriesCollection(seriesLine)
> .Border.linestyle = linestyle
> .MarkerBackgroundColorIndex = linecolor
> .MarkerForegroundColorIndex = linecolor
> End With
> If bHaveLegend Then
> With .Legend.LegendEntries(seriesLine).Font
> .ColorIndex = fontcolor
> .Background = fontbckgrd
> End With
> End If
> End With
>
> End Sub
>
> Private Sub CheckBox1_Click()
> SwitchSeriesLine 1, Me.CheckBox1.Value, True
> End Sub
>
> Private Sub CheckBox2_Click()
> SwitchSeriesLine 2, Me.CheckBox2.Value, True
> End Sub
>
> Private Sub CheckBox3_Click()
> SwitchSeriesLine 3, Me.CheckBox3.Value, True
> End Sub
>
> --
> Hope this helps
> Martin Fishlock
> Please do not forget to rate this reply.
>
>
> "keri" wrote:
>
>> Hi I am getting frustrated with the code for the chart as below.
>> I want the chart series data source to change depending on a checkbox
>> linked to a cell. However I seem to have a problem with the line that
>> changes the values. (I recorded the original code from a macro). I have
>> tried naming the series collection by number but this doesn't work
>> either. When I step through the code and get to either of the lines
>> that say " ......values = "=R" then I get a run time error 1004
>> application or user defined error or a run time error 438 object does
>> not support this property or method.
>>
>>
>> Sheets("chart").Select
>> ActiveSheet.ChartObjects("Chart 1").Activate
>> ActiveChart.ChartArea.Select
>>
>> If ActiveSheet.Range("a3") = True Then
>> ActiveChart.seriescollection("HAMILTON").Select
>> ActiveChart.seriescollection("HAMILTON").values = "=R28C4:R34C4"
>> Else: ActiveChart.seriescollection("HAMILTON").values =
>> "=R2C2:R20C2"
>>
>> I'd appreciate any advice anyone can give me as to where I am going
>> wrong. Thanks.
>>
>>

microsoft.public.excel.programming

Chart Code

keri

bda75

Peter T

Martin Fishlock

Jon Peltier

Jon Peltier

x Login to ForumsZone