If Loops and Chart Series

I have a macro that takes a .csv file with lots of data, then selects the important data and plots a graph and outputs a .xlsb file.

There are some check boxes on a user form that allow the addition of some points of interest on the graph; each point of interest is plotted as a new series and it works great when all four options are selected.

If, however, only the last two points of interest are required, then check boxes 1 & 2 are left blank and the macro falls over..

It fails to work because the series are numbered in the syntax, but if the first two points weren't plotted then those series dont exist, so when I refer to the series that I do want, it should be 2 numbers lower in name.

Is these something clever that can be done here with series indexing?

'-JS- Adds "Fire Point Want" on to graph
 If Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Shapes("Check Box 1").ControlFormat.Value = 1 Then
    ActiveChart.SeriesCollection.NewSeries
    With ActiveChart.FullSeriesCollection(3)
        .Name = "Fire point want"
        .XValues = Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Cells(7, 4).Value
        .Values = NomFire
        .ChartType = xlXYScatter
        .MarkerStyle = 8
        .MarkerForegroundColorIndex = -4142
        .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
        .ApplyDataLabels
        With .DataLabels
            .ShowSeriesName = True
            .ShowValue = False
            .Position = xlLabelPositionAbove
            .Orientation = xlUpward
            .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 176, 80)
        End With
    End With
Else 'do nothing
End If
   
'-JS- Adds "Fire Point Need" on to graph
If Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Shapes("Check Box 2").ControlFormat.Value = 1 Then
    ActiveChart.SeriesCollection.NewSeries
    With ActiveChart.FullSeriesCollection(4)
        .Name = "Fire point need"
        .XValues = Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Cells(9, 4).Value
        .Values = NomFire
        .ChartType = xlXYScatter
        .MarkerStyle = 8
        .MarkerForegroundColorIndex = -4142
        .Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        .ApplyDataLabels
        With .DataLabels
            .ShowSeriesName = True
            .ShowValue = False
            .Position = xlLabelPositionAbove
            .Orientation = xlUpward
            .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
        End With
    End With
Else 'do nothing
End If

'-JS- Adds "Refill point want" on to graph
If Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Shapes("Check Box 5").ControlFormat.Value = 1 Then
    ActiveChart.SeriesCollection.NewSeries
    With ActiveChart.FullSeriesCollection(5)
        .Name = "Refill point want"
        .XValues = Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Cells(15, 4).Value
        .Values = RefillEOL
        .ChartType = xlXYScatter
        .MarkerStyle = 8
        .MarkerForegroundColorIndex = -4142
        .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
        .ApplyDataLabels
        With .DataLabels
            .ShowSeriesName = True
            .ShowValue = False
            .Position = xlLabelPositionAbove
            .Orientation = xlUpward
            .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 176, 80)
        End With
    End With
Else 'do nothing
End If

'-JS- Adds "Refill point need" on to graph
If Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Shapes("Check Box 6").ControlFormat.Value = 1 Then
    ActiveChart.SeriesCollection.NewSeries
    With ActiveChart.FullSeriesCollection(6)
        .Name = "Refill point need"
        .XValues = Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Cells(17, 4).Value
        .Values = RefillEOL
        .ChartType = xlXYScatter
        .MarkerStyle = 8
        .MarkerForegroundColorIndex = -4142
        .Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        .ApplyDataLabels
        With .DataLabels
            .ShowSeriesName = True
            .ShowValue = False
            .Position = xlLabelPositionAbove
            .Orientation = xlUpward
            .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
        End With
    End With
Else 'do nothing
End If

End Sub

So I have found a work around for this problem...

Code below..

'-JS- Adds "Refill point want" on to graph
If Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Shapes("Check Box 5").ControlFormat.Value = 1 Then
    ActiveChart.SeriesCollection.NewSeries
    With ActiveChart.FullSeriesCollection(5)
        .Name = "Refill point want"
        .XValues = Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Cells(15, 4).Value
        .Values = RefillEOL
        .ChartType = xlXYScatter
        .MarkerStyle = 8
        .MarkerForegroundColorIndex = -4142
        .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
        .ApplyDataLabels
        With .DataLabels
            .ShowSeriesName = True
            .ShowValue = False
            .Position = xlLabelPositionBelow
            .Orientation = xlDownward
            .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 176, 80)
        End With
    End With
Else: ActiveChart.SeriesCollection.NewSeries
    With ActiveChart.FullSeriesCollection(5)
        .Name = "Refill point want"
        .XValues = Workbooks("180806_Convert_HSV_CSV_to_Graph.xlsb").Sheets("Variables").Cells(15, 4).Value
        .Values = NomFire
        .ChartType = xlXYScatter
        .MarkerStyle = -4142
    End With
End If

I'm basically hiding the series, but they still exist.

not elegant, but works for me.