changes to chart series numbers

[sulley333]

I am creating a chart but won't always know how many data series I will have...does anyone know how to adjust the code below so that it can account for changes in the number of series?

    ActiveChart.PlotArea.Select
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='Comparison Analysis Data'!$A$6:$A$" & chartrow - 1
    ActiveChart.SeriesCollection(1).Values = "='Comparison Analysis Data'!$B$6:$B$" & chartrow - 1
    ActiveChart.SeriesCollection(2).Values = "='Comparison Analysis Data'!$C$6:$C$" & chartrow - 1

 

[GlennUK]

What criteria is the macro supposed to use to test for how much data there is?

 

[sulley333]

For iZ = 0 To ctlz.Object.ListCount - 1
If ctlz.Object.Selected(iZ) Then
sSelectedBrand = ctlz.Object.List(iZ)
Exit For
End If
Next

This code is then used in the code to create a pivot table:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
.PivotItems(sSelectedBrand).Visible = True
 
'Set that item visible all others not - procedure appears to be duplicative, but
'have to ensure at least one item is visible.
For Each pI In .PivotItems
Select Case UCase(pI.Name)
Case UCase(sSelectedBrand)
pI.Visible = True
Case Else
pI.Visible = False
End Select
Next pI
 
'Turn on all that are selected
For iZ = 0 To ctlz.Object.ListCount - 1
If ctlz.Object.Selected(iZ) Then
.PivotItems(ctlz.Object.List(iZ)).Visible = True
End If
Next
End With

But for the actual chart I just use a column number variable created from the pivot table data. I don't know if any of this answers your question really!

 

[GlennUK]

What I mean is that your data, as previously mentioned:
'Comparison Analysis Data'!$B$6:$B$" & chartrow - 1
'Comparison Analysis Data'!$C$6:$C$" & chartrow - 1
etc
is to be added until what?

 

[sulley333]

When I originally created this chart I was only anticipating two series so I had hard coded them in...now I need it to be a little more dynamic and be flexible depending on how many categories are selected...any ideas?

 

[GlennUK]

So, "on how many categories are selected" ... being what exactly? You'll have to explain.

 

[sulley333]

The user will have a list of around 6-7 categories to choose from...before I had the ability to only select two from two different drop down box. Now I have a multi-select box that gives them the possibility to select as many as they would like to see in the chart. Does that help??

 

[GlennUK]

So, what do you do with the selections from the multi-select box at the moment?

 

[sulley333]

I use them to create the data within a pivot table which creates the chart data...now I am stumped as to how I am going to build the chart without knowing how many series I will have.

 

[GlennUK]

I use them to create the data within a pivot table which creates the chart data...now I am stumped as to how I am going to build the chart without knowing how many series I will have.

How do you use them exactly?