Chart code

[keri]

Dim checkbox1 As Boolean
Worksheets("sheet3").ChartObjects(3).Activate
ActiveChart.SeriesCollection(1).XValues = "=Sheet3!R3C1:R27C1"
If checkbox1 = True Then
ActiveChart.SeriesCollection(1).Values = "=Sheet3!R3C2:R27C2"
Else: ActiveChart.SeriesCollection(1).Values = "0"
ActiveChart.SeriesCollection(1).Name = "=Sheet3!R2C2"
ActiveChart.SeriesCollection(2).Values = "=Sheet3!R3C3:R27C3"
ActiveChart.SeriesCollection(2).Name = "=Sheet3!R2C3"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
ActiveChart.HasDataTable = False
End If
End Sub

I have recorded most of the above code from a macro. The first line
(worksheets("sheet3") etc) generates an error and I haven't a clue what
the heck i am doing wrong now.

Second to this I have added in the If Else rows as I want a data series
only to appear if a checkbox is selected. Is this correct?

 

[Guest]

Keri,

The line

Worksheets("sheet3").ChartObjects(3).Activate

is trying to 'select' the third chart on 'sheet3' and the third chart is not
there.

You need to set up the chart and I would also give it a name so that you can
refer to it by name.

On your second question have you tried using the visible. ie

ActiveChart.visible = (checkbox1 = True)

and remove the if statement.

 

[keri]

Thanks but unfortunately I am wanting 26 different data series that are
visible / not visible depending on their checkboxes. I can't use the
visible cells only option for other reasons. I figured the best way to
do it was to set if the click box was not checked (false) that the
series data was set to 0. Please correct me if there is another way.

 

[Peter T]

If (?) the objective is to hide your series why not simply exclude it when
making the chart or delete it later. Or if it's a 'fill' type and you want
to leave it's location intact but in effect make it invisible, record a
macro while formatting its fill and line (border) to none.

If for some reason you particularly need to make all values zero and not
linked to cells, with 'sr' a reference to your series -

ReDim naVals(1 To sr.Points.Count) As Long
sr.Values = naVals

Obviously you'd need to populate the array for any other value(s) other than
zero. There could be problems if user amends the chart, also this method
would be limited to about 115 points assuming applying single digit values
like 0.

Regards,
Peter T

 

[Jon Peltier]

Check out the non-VBA checkbox example on this web page:

http://peltiertech.com/Excel/Charts/ChartByControl.html

A few coding suggestions:

Don't combine lines of code using a colon ). It makes the code harder to
follow, especially in the "Else: blah blah" line in your code.

This line suffers from trying to assign the series values to a string:
ActiveChart.SeriesCollection(1).Values = "0"

You could set it to a literal representation of an array of one or more
zeros, e.g.:
ActiveChart.SeriesCollection(1).Values = "={0}"
ActiveChart.SeriesCollection(1).Values = "={0,0,0,0}"

Or you could point the values at an empty range of the appropriate size:
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R1C26:R25C26"

- Jon