Optional data series in chart (VBA)

[Filip88]

Hello,
I would like to ask you about issue with excel, i am having.

In worksheet, i have two options, to be chosen by user, each option add additional data series in chart.

The problem is that there is combination between both options and VBA in default create data series according to indexes. Therefore if there is placed only the 1st option, it adds SeriesCollection(6), if there are chosen both options, the 1st option add SeriesCollection(6), the 2nd option add SeriesCollection(7). However the problem is when i want to choose only the 2nd option and the 1st is blank, excel creates the 2nd option as seriescollection(6), but I need it to be remained as SeriesCollection(7) or somehow have its own fixed name, which I can refer to.

Am i somehow able to set up the 1st option as SeriesCollection 6 and the 2nd option as SeriesCollection(7), regardless it was chosen or not?

I know i can use SeriesCollection.Count to test how many collections are in chart and process the code, but more suitable would be to have fixed series collections of each option to be placed optionally.

Option1

If Worksheets("main").Range("c44") = "option1" Then
 
ActiveSheet.ChartObjects("Graf 10").Activate
Application.CutCopyMode = False
 
ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).Values = _
        "=data"
     ActiveChart.SeriesCollection(6).Select
     ActiveChart.SeriesCollection(6).AxisGroup = 2
    ActiveChart.SeriesCollection(6).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
    End With
End if

Option2

If Worksheets("main").Range("F44") = "Option2“ Then
   
     ActiveSheet.ChartObjects("Graf 10").Activate  
     Application.CutCopyMode = False
     ActiveChart.SeriesCollection.NewSeries
    
     ActiveChart.SeriesCollection(7).Values = _
         "=data2"
      ActiveChart.SeriesCollection(7).Select
      ActiveChart.SeriesCollection(7).AxisGroup = 2
     ActiveChart.SeriesCollection(7).Select
     With Selection.Format.Line
         .Visible = msoTrue
         .ForeColor.RGB = RGB(155, 0, 0)
     End With
     End If

Thank you for help

 

[Jon Peltier]

You can reference the newly added series directly (and clean up some unneeded "selects"):

If Worksheets("main").Range("c44") = "option1" Then
    ActiveSheet.ChartObjects("Graf 10").Activate
    Application.CutCopyMode = False
 
    With ActiveChart.SeriesCollection.NewSeries
        .Values = "=data"
        .AxisGroup = 2
        ActiveChart.SeriesCollection(6).Select
        With .Format.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
        End With
    End With
End if

and

If Worksheets("main").Range("F44") = "Option2“ Then
     ActiveSheet.ChartObjects("Graf 10").Activate  
     Application.CutCopyMode = False

     With ActiveChart.SeriesCollection.NewSeries
         .Values = "=data2"
         .AxisGroup = 2
         With .Format.Line
             .Visible = msoTrue
             .ForeColor.RGB = RGB(155, 0, 0)
         End With
     End With
 End If

 

[Filip88]

Thank you for your reply. Well I did some programming and I have decided to used SeriesCollection.Count to check number of dataseries each time when some option is chosen by user, then I added formula +1 to each chose option. So when user choose either option 1 or option 2, I check number of current series and then increase it by 1 to create new one, the same goes when it is removed, it checks number of series and then decrease by 1. It works quite fine, so problem solved