using checkboxes to plot certain data series on one plot

Hi - this post is not as long as it seems, I've included some code for reference so please keep reading

I have a bunch of data sheets that are identical in format (other than the number of rows). I'm trying to make a bunch of different graphs for the data sheet (same graphs for each worksheet). In order to make comparing data series easier, I'm trying to have a list of check boxes representing all of my different worksheets, and through these checkboxes, be able to select which of these data series I want to plot on the same graph.
To keep things simple, I've just been trying to work with two worksheets and one graph.

This is the code for my first checkbox (which works okay on it's own):

Formula:

Private Sub CheckBox1_Click()



Dim lastdatapoint1 As Long

If CheckBox1.Value = True Then

Set SheetName = Worksheets("FB1")



With Worksheets("Plots")

'delete any other chart on the sheet before proceding

    On Error Resume Next

    .ChartObjects.Delete

End With

     

lastdatapoint1 = SheetName.Cells(Rows.Count, 2).End(xlUp).Row



Charts.Add

ActiveChart.Name = "Chart1"

With ActiveChart

    .ChartType = xlXYScatterSmooth

    .SetSourceData Source:=SheetName.Range("$R$3:$R" & lastdatapoint1)

    .SeriesCollection(1).XValues = SheetName.Range("$B$3:$B" & lastdatapoint1)

    .SeriesCollection(1).Name = SheetName.Range("R1")

    'x axis label spacing

    .Axes(xlCategory).TickMarkSpacing = 10

    .Axes(xlCategory).TickLabelSpacing = 10

    .HasTitle = True

    .ChartTitle.Text = SheetName.Range("R1")

    'axes titles

    .Axes(xlCategory, xlPrimary).HasTitle = True

    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"

    .Axes(xlValue, xlPrimary).HasTitle = True

    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "T_SEV_IN (C)"

    .Location Where:=xlLocationAsObject, Name:="Plots"

End With



'make the graph area bigger

With ActiveChart.Parent

    .Height = 325

    .Width = 500

    .Top = Range("C5").Top

    .Left = Range("C5").Left

End With



End If



End Sub

My second checkbox is the one giving me problems...I think I've done a few things wrong here, but I'm a complete rookie and am not sure how to go about fixing them.

Formula:

Private Sub CheckBox2_Click()



Dim lastdatapoint2 As Long

If CheckBox2.Value = True Then

Set SheetName2 = Worksheets("FB2")

     

lastdatapoint2 = SheetName2.Cells(Rows.Count, 2).End(xlUp).Row



Charts("Chart1").Activate



With ActiveChart

    .SeriesCollection.NewSeries

    .SeriesCollection(2).Name = SheetName2.Range("R1")

    .SeriesCollection(2).Values = SheetName.Range("$R$3:$R" & lastdatapoint2)

    .SeriesCollection(2).XValues = SheetName2.Range("$B$3:$B" & lastdatapoint2)

End With



End If



End Sub

- For starters, I get a subscript out of range error for the line Charts("Chart1").Activate
- I don't know how to set up my code so that if FB1 data is not selected, I still create a plot (without creating two plots by adding a chart in the checkbox 2 code)
- Another thing that is kind of annoying is that the second i select the checkbox for my first data sheet, excel graphs it and jumps to the "plots" worksheet so i have to go back to my grapher sheet to select another series to graph...should i just activate the graphical control sheet at the end of my code? it would be better if i could implement a command button that is linked to the selection of check boxes so that the selection boxes don't execute an action the second they're clicked
- is it possible to have a "select" function with different cases for all my checkboxes rather than having a different sub for each? I think this would be best, then I could maybe create the shell for a graph before selecting which data series to plot...

I'll post updates as I change things around. If someone could just point me in the right direction as to how to structure this sort of idea that would be great. I have some coding experience, just not with VBA.

Thanks in advance,

Dan

Not sure if this is the most condensed way to do it, but this works. I created a macro for the command button, referencing the check boxes

Formula:

Private Sub CommandButton2_Click()



Dim lastdatapoint1 As Long

Dim lastdatapoint2 As Long

Dim SheetName1 As Worksheet

Dim SheetName2 As Worksheet



With Worksheets("Plots")

'delete any other chart on the sheet before proceding

    On Error Resume Next

    .ChartObjects.Delete

End With



Charts.Add



With ActiveChart

    .Name = "Chart1"

    .ChartType = xlXYScatterSmooth

    'x axis label spacing

    .Axes(xlCategory).TickMarkSpacing = 10

    .Axes(xlCategory).TickLabelSpacing = 10

    .HasTitle = True

    .ChartTitle.Text = SheetName.Range("R1")

    'axes titles

    .Axes(xlCategory, xlPrimary).HasTitle = True

    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"

    .Axes(xlValue, xlPrimary).HasTitle = True

    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "T_SEV_IN (C)"

    .Location Where:=xlLocationAsObject, Name:="Plots"

End With



With ActiveChart.Parent

    .Height = 325

    .Width = 500

    .Top = Range("C5").Top

    .Left = Range("C5").Left

End With



If CheckBox1.Value = True Then

Set SheetName1 = Worksheets("FB1")

lastdatapoint1 = SheetName1.Cells(Rows.Count, 2).End(xlUp).Row



With ActiveChart

    .SetSourceData Source:=SheetName1.Range("$R$3:$R" & lastdatapoint1)

    .SeriesCollection(1).XValues = SheetName1.Range("$B$3:$B" & lastdatapoint1)

    .SeriesCollection(1).Name = SheetName1.Range("R1")

End With



End If



If CheckBox2.Value = True Then

Set SheetName2 = Worksheets("FB2")

lastdatapoint2 = SheetName2.Cells(Rows.Count, 2).End(xlUp).Row



With ActiveChart

    .SeriesCollection.NewSeries

    .SeriesCollection(2).Name = SheetName2.Range("R1")

    .SeriesCollection(2).Values = SheetName2.Range("$R$3:$R" & lastdatapoint2)

    .SeriesCollection(2).XValues = SheetName2.Range("$B$3:$B" & lastdatapoint2)

End With



End If



End Sub