Using loops for Checkbox

Question

I am trying to generate a GUI in excel which plots and unplots data on a single chart depending on whether the checkbox is selected or not.

The vba script :

 Private Sub UserForm_Initialize()

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries

    ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$A$21:$A$23"
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!$B$21:$B$23"
    With ActiveChart.Parent
        .Height = 300 ' resize
        .Width = 600 ' resize
        .Top = 100 ' reposition
        .Left = 100 ' reposition
    End With
End Sub

Private Sub CheckBox1_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "3bar,pH7,30C"
    ActiveChart.SeriesCollection(2).XValues = "=Sheet1!$D$21:$D$223"
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!$E$21:$E$223"
End Sub

Private Sub CheckBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ActiveChart.SeriesCollection(2).Delete
End Sub

Private Sub CheckBox2_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Name = "3bar,pH7,20C"
    ActiveChart.SeriesCollection(3).XValues = "=Sheet1!$G$21:$G$223"
    ActiveChart.SeriesCollection(3).Values = "=Sheet1!$H$21:$H$223"
End Sub

Private Sub CheckBox3_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(4).Name = "3bar,pH7,10C"
    ActiveChart.SeriesCollection(4).XValues = "=Sheet1!$J$21:$J$223"
    ActiveChart.SeriesCollection(4).Values = "=Sheet1!$K$21:$K$223"
End Sub

Private Sub CheckBox4_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(5).Name = "3bar,pH5,30C"
    ActiveChart.SeriesCollection(5).XValues = "=Sheet1!$M$21:$M$223"
    ActiveChart.SeriesCollection(5).Values = "=Sheet1!$N$21:$N$223"
End Sub

Private Sub CheckBox5_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).Name = "3bar,pH5,20C"
    ActiveChart.SeriesCollection(6).XValues = "=Sheet1!$P$21:$P$223"
    ActiveChart.SeriesCollection(6).Values = "=Sheet1!$Q$21:$Q$223"
End Sub

Private Sub CheckBox6_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(7).Name = "3bar,pH5,20C(DI)"
    ActiveChart.SeriesCollection(7).XValues = "=Sheet1!$S$21:$S$223"
    ActiveChart.SeriesCollection(7).Values = "=Sheet1!$T$21:$T$223"
End Sub

Private Sub CheckBox7_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(8).Name = "3bar,pH5,20C(HNO3)"
    ActiveChart.SeriesCollection(8).XValues = "=Sheet1!$V$21:$V$223"
    ActiveChart.SeriesCollection(8).Values = "=Sheet1!$W$21:$W$223"
End Sub

Private Sub CheckBox8_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(9).Name = "3bar,pH5,10C"
    ActiveChart.SeriesCollection(9).XValues = "=Sheet1!$Y$21:$Y$223"
    ActiveChart.SeriesCollection(9).Values = "=Sheet1!$Z$21:$Z$223"
End Sub

Private Sub CheckBox9_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(10).Name = "3bar,pH9,20C"
    ActiveChart.SeriesCollection(10).XValues = "=Sheet1!$AB$21:$AB$223"
    ActiveChart.SeriesCollection(10).Values = "=Sheet1!$AC$21:$AC$223"
End Sub

Private Sub CheckBox10_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(11).Name = "3bar,pH9,10C"
    ActiveChart.SeriesCollection(11).XValues = "=Sheet1!$AE$21:$AE$223"
    ActiveChart.SeriesCollection(11).Values = "=Sheet1!$AF$21:$AF$223"
End Sub

Private Sub CheckBox11_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(12).Name = "5bar,pH5,20C"
    ActiveChart.SeriesCollection(12).XValues = "=Sheet1!$AH$21:$AH$223"
    ActiveChart.SeriesCollection(12).Values = "=Sheet1!$AI$21:$AI$223"
End Sub

Private Sub CheckBox12_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(13).Name = "1bar,pH5,20C"
    ActiveChart.SeriesCollection(13).XValues = "=Sheet1!$AK$21:$AK$223"
    ActiveChart.SeriesCollection(13).Values = "=Sheet1!$AL$21:$AL$223"
End Sub

Private Sub CheckBox13_Click()
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(14).Name = "0bar,pH5,20C"
    ActiveChart.SeriesCollection(14).XValues = "=Sheet1!$AN$21:$AN$223"
    ActiveChart.SeriesCollection(14).Values = "=Sheet1!$AO$21:$AO$223"
End Sub

A total of almost 60 checkbox definitions - 30 for clicking each of them and another 30 for unclicking them. .

The following are my issues:

(1) Instead of hardcoding for each 30 of the checkboxes, is it possible to define them using loop ? I am not sure how the checkbox name (CheckBox1, Checkbox2 etc.) can be defined as a variable and also how to create a generalized form for the commands

(2) The attempt to delete the dataset doesn't seem to work. eg: If I delete SeriesCollection(2), vba renumbers all other datasets which makes their id number irretrievable.

I would really appreciate any guidance regarding the same.

Answer 1

• Create a custom class that would handle a group event for all the comboboxes.
• Add a class level collection to the userform to keep references to the custom class alive.
• Create a table to lookup settings based on the Checkboxes index

Lookup Table Download

ControlWrapper Class Code

Public WithEvents ChartCheckbox As MSForms.CheckBox
Public index As Long

Private Sub ChartCheckbox_Click()
    MsgBox index
    With ActiveChart
        .ChartType = xlXYScatterLinesNoMarkers
        .SeriesCollection.NewSeries
        .SeriesCollection(index).Name = Application.VLookup(index, Range("ChartSettings"), 3, False)
        .SeriesCollection(index).XValues = "=" & Application.VLookup(index, Range("ChartSettings"), 4, False)
        .SeriesCollection(index).Values = "=" & Application.VLookup(index, Range("ChartSettings"), 5, False)
    End With
End Sub

Userform Code

Private ControlsCollection As Collection

Private Sub UserForm_Initialize()

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SeriesCollection.NewSeries

    ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$A$21:$A$23"
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!$B$21:$B$23"
    With ActiveChart.Parent
        .Height = 300    ' resize
        .Width = 600    ' resize
        .Top = 100    ' reposition
        .Left = 100    ' reposition
    End With

    ' Initiate the Controls Collection

    Dim ctrl
    Dim wrapper As ControlWrapper
    Set ControlsCollection = New Collection

    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.CheckBox Then
            Set wrapper = New ControlWrapper
            Set wrapper.ChartCheckbox = ctrl
            wrapper.index = Replace(ctrl.Name, "CheckBox", "")
            ControlsCollection.Add wrapper
        End If
    Next
End Sub