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

The first thing I'd recommend is following a proper naming convention. "CheckBox11" and "DTPciker1" are really vague and once you get further into your code, you'll forget which control is which. I would recommend naming them something that relates the two control together, like "firstDate" and "firstDateDTP". My alternate answer below uses this approach.

You could make a public function that enables the DTPicker based upon the checkbox's value.

Public Function EnableDTPicker(myPicker as String, enableBool as Boolean)

    UserFormName.Controls(myPicker).Enabled = enableBool

End Function

Then, you can call the function in your CheckBox123_Click() subs like this:

Private Sub CheckBox123_Click()
    EnableDTPicker("thePickerName", CheckBox123.Value)
End Sub

Alternatively, you could make a timer event that runs x number of seconds that just loops through the controls and performs the checks as needed. See this page on how to set up the timer. Using the code in the link shown, You could do something along the lines of:

'Put this in Workbook events
Private Sub Workbook_Open()
    alertTime = Now + TimeValue("00:00:01")
    Application.OnTime alertTime, "EventMacro"
    UserForm1.Show
End Sub

'Put this in a Module
Public Sub EventMacro()
    With UserForm1
        For each ctrl in .Controls
            If TypeName(ctrl) = "CheckBox" Then
                'The code below assumes the naming convention outlined above is followed
                .Controls(ctrl.Name & "DTP").Enabled = ctrl.Value
            End If
        Next ctrl
    End With

    alertTime = Now + TimeValue("00:00:01")
    Application.OnTime alertTime, "EventMacro"
End Sub

Answer 2

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