How to evaluate checkboxes with one routine?

My apologies. This is Excel. I answered the prompts asking about Windows, MS Office and Excel when I submitted.

Currently there are two options to populate the checkboxes:

1. Select the CHECK ALL DATA, which will in turn place a checkmark in all the other checkboxes.
2. Select only the choices I need to execute.

If I select CHECK ALL DATA and this deselect the one or more options that I do not want, the CHECK ALL DATA checkbox remains checked. I wanted a way to deselect the CHECK ALL DATA if I chose to deselect one or more options.

The checkboxes are referenced as CheckBox1 - Checkbox18. I have no need at this time to assign any code to each checkbox via On Click, except for the Clear All Data box. I am unfamiliar with the HandleDataSelectionChange.

The execution when ENTER is clicked is handled within one routine via SELECT CASE inside a For-Next Loop.

Dear Les M
Thank you for your response; your feedback is very important to me.

I am glad the proposed solution resonates with you.

Here are a clean, consolidated version of the code and the implementation steps for clarity and easy deployment. 

The strategy remains the same: use a central routine to monitor the status of the 17 subordinate checkboxes (2 through 18) and instantly update the master checkbox (Checkbox1).

1. The Core Logic Subroutine

 This subroutine performs the crucial check. You will place this function in the same code module as your other checkbox procedures.

Private Sub UpdateCheckAllStatus()
    ' Checks if all sub-checkboxes (2-18) are checked and updates CheckBox1.
    
    Dim i As Integer
    Dim AllChecked As Boolean
    AllChecked = True ' Start by assuming all are checked
    
    ' Loop through CheckBox2 to CheckBox18
    For i = 2 To 18
        ' Use Me.Controls() for UserForm or SheetName.Controls() for Worksheet ActiveX
        If Me.Controls("CheckBox" & i).Value = False Then
            AllChecked = False
            Exit For ' Optimization: Stop checking once one is found unchecked
        End If
    Next i
    
    ' Update the main Checkbox (Checkbox1) status
    Me.CheckBox1.Value = AllChecked
End Sub

2. Code for the Master Checkbox (Checkbox1_Click)

This code runs when the user clicks "CHECK ALL DATA" to quickly toggle the state of all other checkboxes.

Private Sub CheckBox1_Click()
    ' Sets all sub-checkboxes to the same state as CheckBox1.
    
    Dim i As Integer
    For i = 2 To 18
        ' Synchronize the state of sub-checkboxes (2-18)
        Me.Controls("CheckBox" & i).Value = Me.CheckBox1.Value
    Next i
End Sub

3. Code for the Individual Checkboxes (Checkbox2 to Checkbox18)

 This is the most repetitive step, but it is necessary. For every individual checkbox, you must call the centralized routine so that Checkbox1's status is immediately updated upon any change.

Private Sub CheckBox2_Click()
    UpdateCheckAllStatus
End Sub

Private Sub CheckBox3_Click()
    UpdateCheckAllStatus
End Sub

' ... You must repeat the pattern below for CheckBox4_Click() through CheckBox18_Click()
' Private Sub CheckBox4_Click()
'     UpdateCheckAllStatus
' End Sub
' ...
' Private Sub CheckBox18_Click()
'     UpdateCheckAllStatus
' End Sub

This logic ensures a seamless user experience: if the user clicks Checkbox1, everything is checked; if they later deselect any single option, the system instantly sets Checkbox1 to unchecked.

Please proceed with implementing this code. If you face any issues feel free to let me know, and we can troubleshoot the implementation details.

Hi Les M

Just writing to see how things are going with this issue.

Have you had a chance to check the replies provided?

Any update would be appreciated.

Hi Les M
Thank you for your response!

Your feedback is very important to me. You’re correct that the UpdateCheckAllStatus() procedure will not run automatically, it needs to be triggered by an event. Typically, this happens when any of the individual checkboxes (CheckBox1 through CheckBox17) is clicked.

You can call UpdateCheckAllStatus inside the Click event of each checkbox. For example:

Private Sub CheckBox1_Click()
    UpdateCheckAllStatus
End Sub

Repeat this for CheckBox2 through CheckBox17. This ensures that whenever a checkbox is checked or unchecked, the routine runs and updates CheckBox18 accordingly.

Instead of writing 17 separate event procedures, you can use a Class Module to handle all checkbox events dynamically:

Create a Class Module (name it clsCheckBoxHandler):

Public WithEvents chk As MSForms.CheckBox

Private Sub chk_Click()
    UpdateCheckAllStatus
End Sub

In your UserForm code:

Dim chkHandlers() As New clsCheckBoxHandler

Private Sub UserForm_Initialize()
    Dim i As Integer
    ReDim chkHandlers(1 To 17)
    For i = 1 To 17
        Set chkHandlers(i).chk = Me.Controls("CheckBox" & i)
    Next i
End Sub

Private Sub UpdateCheckAllStatus()
    Dim i As Integer
    Dim AllChecked As Boolean
    AllChecked = True
    For i = 1 To 17
        If Me.Controls("CheckBox" & i).Value = False Then
            AllChecked = False
            Exit For
        End If
    Next i
    Me.CheckBox18.Value = AllChecked
End Sub

I hope this information is helpful to you. Please try these steps and let me know how it goes so we can find the best solution together. Warm thanks!