Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart code
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 Whe=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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart code
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 Whe=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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart code
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart code
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" wrote in message ups.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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart code
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "keri" wrote in message ups.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 Whe=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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart Object VB Code | Charts and Charting in Excel | |||
VBA code for chart | Excel Programming | |||
Flow chart of code? Is there a way to produce a graphical flow chart? | Excel Programming | |||
Please Help With Chart Code | Excel Programming | |||
Help with following code creating chart | Excel Programming |