[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Chart code

keri

12/14/2006 11:22:00 PM

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?
4 Answers

Martin Fishlock

12/15/2006 3:08:00 AM

0

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.


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


"keri" wrote:

> 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?
>
>

keri

12/15/2006 8:35:00 AM

0


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

12/15/2006 9:20:00 AM

0

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

"keri" <keri.dowson@diageo.com> wrote in message
news:1166171692.139018.237690@f1g2000cwa.googlegroups.com...
>
> 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.
>

Jon Peltier

12/15/2006 2:29:00 PM

0

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

http://peltiertech.com/Excel/Charts/ChartByCo...

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://Pelti...
_______


"keri" <keri.dowson@diageo.com> wrote in message
news:1166138525.402507.277070@73g2000cwn.googlegroups.com...
> 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?
>