Code to account for all checkboxes in a userform?

Question

I have code on a userform that contains several checkboxes and several DTPickers.

The code looks like so:

Private Sub CheckBox11_Click()
If CheckBox11.Value = True Then
    DTPicker22.Enabled = True
Else
    DTPicker22.Enabled = False
End If
End Sub

Private Sub CheckBox12_Click()
If CheckBox12.Value = True Then
    DTPicker24.Enabled = True
Else
    DTPicker24.Enabled = False
End If
End Sub 

The Userform contains a lot of checkboxes that have clauses next to them. Upon their completion the DTPicker will enable entering the date of completion.

Whilst this does what I want, it only enables one DTPicker when the checkbox is ticked per private sub. There has to be some way to make this so I wouldn't need to create different private subs for every checkbox click event.

Could you also tell me where to put it, as in, what event?

Answer 1

A "control array" is the typical approach for something like this.

See: http://www.siddharthrout.com/index.php/2018/01/15/vba-control-arrays/

eg:

Class module clsEvents

Option Explicit

'Handle events for a checkbox and a date control, associated with a worksheet cell
Private WithEvents m_CB As MSForms.CheckBox
Private WithEvents m_DP As DTPicker
Private m_dateCell As Range

'set up the controls and the cell
Public Sub Init(cb As MSForms.CheckBox, dp As DTPicker, rng As Range)
    
    Set m_CB = cb
    Set m_DP = dp
    Set m_dateCell = rng
    
    If rng.Value > 0 Then
        cb.Value = True
        m_DP.Value = rng.Value
    Else
        cb.Value = False
    End If
    
    m_DP.CustomFormat = "dd/MM/yyyy"
    
End Sub

Private Sub m_CB_Change()
    m_DP.Enabled = (m_CB.Value = True)
End Sub

Private Sub m_DP_Change()
    m_dateCell.Value = m_DP.Value 'update the cell
End Sub

Userform:

Option Explicit

Dim colObj As Collection 'needs to be a Global to stay in scope

Private Sub UserForm_Activate()
    Dim obj As clsEvents, i As Long, ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Set colObj = New Collection
    'loop over controls and create a class object for each set
    ' 3 pairs of controls on my test form...
    For i = 1 To 3
        Set obj = New clsEvents
        obj.Init Me.Controls("CheckBox" & i), _
                  Me.Controls("DTPicker" & i), _
                  ws.Cells(i, "B")
        colObj.Add obj
    Next i

End Sub