Create a Checkbox array

[nouseforaname1982]

I have a userform with 29 check boxes. I would like to apply the same code to all check boxes. So instead of having checkbox1; checkbox2... I will have something like checkbox(i).
Is this possible?

I have posted some example code.

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
Cells(2, 1 + 6).EntireColumn.Hidden = False
Else
Cells(2, 1 + 6).EntireColumn.Hidden = True
End If

End Sub

Private Sub CheckBox2_Click()

If CheckBox2.Value = True Then
Cells(2, 2 + 6).EntireColumn.Hidden = False
Else
Cells(2, 2 + 6).EntireColumn.Hidden = True
End If

End Sub

Private Sub CheckBox3_Click()

If CheckBox3.Value = True Then
Cells(2, 3 + 6).EntireColumn.Hidden = False
Else
Cells(2, 3 + 6).EntireColumn.Hidden = True
End If

End Sub

 

[John_w]

Here's one way:

Private Sub CheckBox1_Click()
    CB_common Me.CheckBox1
End Sub

Private Sub CheckBox2_Click()
    CB_common Me.CheckBox2
End Sub

Private Sub CheckBox3_Click()
    CB_common Me.CheckBox3
End Sub

Sub CB_common(cb As msforms.CheckBox)
    Dim cbNumber As Integer
    cbNumber = CInt(Mid(cb.Name, Len("CheckBox") + 1, 2))
    If cb.Value = True Then
        Cells(2, cbNumber + 6).EntireColumn.Hidden = False
    Else
        Cells(2, cbNumber + 6).EntireColumn.Hidden = True
    End If
End Sub

 

[nouseforaname1982]

That will limit the code some. Thanks. If there if there is a way to get rid of the multiple private subs that that would be optimal.

 

[John_w]

Create a class event handler to handle events for your checkboxes. There are examples on the internet - search for class checkbox withevents.

 

[nouseforaname1982]

Create a class event handler to handle events for your checkboxes. There are examples on the internet - search for class checkbox withevents.

Thanks! This is what I was really looking for. Please see below for my successful code.

In Class1:

Public WithEvents CheckBoxGroup As MSForms.CheckBox

Private Sub CheckBoxGroup_Click()

    Dim cbNumber As Integer
    cbNumber = CInt(Mid(CheckBoxGroup.Name, Len("CheckBox") + 1, 2))
    If CheckBoxGroup.Value = True Then
        Cells(2, cbNumber + 6).EntireColumn.Hidden = False
    Else
        Cells(2, cbNumber + 6).EntireColumn.Hidden = True
    End If
    
End Sub

In the Module1:

Option Explicit

Dim CheckBoxes() As New Class1

Sub ShowDialog()
    Dim CheckBoxCount As Integer
    Dim ctl As Control
    Dim i As Integer
    
    Application.ScreenUpdating = False
    CheckBoxCount = 0
    For Each ctl In UserForm1.Controls
        If TypeName(ctl) = "CheckBox" Then
                CheckBoxCount = CheckBoxCount + 1
                ReDim Preserve CheckBoxes(1 To CheckBoxCount)
               Set CheckBoxes(CheckBoxCount).CheckBoxGroup = ctl
        End If
    Next ctl
    For i = 29 To 1 Step -1
        If Cells(2, i + 6).EntireColumn.Hidden = True Then CheckBoxes(i).CheckBoxGroup.Value = "False"
    Next i
    
    Application.ScreenUpdating = True
    UserForm1.Show

End Sub