Command Button : Add/Remove data series

[drinkbeer]

Hello,

I have a bar chart that 4 command buttons are connected to. Initially I had successfully set up the command buttons to add a data series on the first click and then remove the data series on the 2nd click. However, when I initially coded these the data series were static (1, 2, 3, etc.). I needed to change these to be dynamic b/c the user won't select them sequentially. They may add one and then skip and add another, change their mind and remove the first one, etc.

Need help in coding the data series to pick up dynamically. Here's the code for each of the buttons. I am an amateur, painfully self taught coder. My role is analytical in nature and creating user friendly charts just helps the business so it's my goal to learn these things.

Private Sub BlockOne_Click()
Dim i As Integer


BlockOne.BackColor = 9125927


Static Count As Integer

Count = Count + 1

If Count = 1 Then
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).Name = "='Time Series - 12mo'!$C$24"
ActiveChart.SeriesCollection(i).Values = "='Time Series - 12mo'!$D$24:$O$24"
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(i).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(39, 64, 139)
.Transparency = 0
.Solid
End With
Range("Q23").Select
End If

If Count = 2 Then
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(i).Delete
Count = 0

End If


End Sub




Private Sub BlockTwo_Click()
Dim i As Integer


Static Count As Integer

Count = Count + 1

If Count = 1 Then
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).Name = "='Time Series - 12mo'!$C$25"
ActiveChart.SeriesCollection(i).Values = "='Time Series - 12mo'!$D$25:$O$25"
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(i).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(128, 128, 128)
.Transparency = 0
.Solid
End With
Range("Q23").Select
End If

If Count = 2 Then
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(i).Delete
Count = 0

End If


End Sub


Private Sub BlockThree_Click()
Dim i As Integer


BlockThree.BackColor = 8983278


Static Count As Integer

Count = Count + 1

If Count = 1 Then
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).Name = "='Time Series - 12mo'!$C$26"
ActiveChart.SeriesCollection(i).Values = "='Time Series - 12mo'!$D$26:$O$26"
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(i).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(238, 18, 137)
.Transparency = 0
.Solid
End With
Range("Q23").Select
End If

If Count = 2 Then
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(i).Delete
Count = 0

End If


End Sub


Private Sub BlockFour_Click()
Dim i As Integer


BlockFour.BackColor = 15651769


Static Count As Integer

Count = Count + 1

If Count = 1 Then
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).Name = "='Time Series - 12mo'!$C$27"
ActiveChart.SeriesCollection(i).Values = "='Time Series - 12mo'!$D$27:$O$27"
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(i).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(185, 211, 238)
.Transparency = 0
.Solid
End With
Range("Q23").Select
End If

If Count = 2 Then
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(i).Delete
Count = 0

End If
End Sub