VBA: <Do something> If all checkboxes on a UserForm have a value of "True"

[Chris The Rock]

I'm pretty new to UserForms, so I'm starting simple.

I've put 6 checkbox objects on the first page of a MultiPage object. I would like to set the Enabled Property of the second page to "True" only if all the checkboxes on the first page have a value of True. If not, or when one of the checkboxes becomes unchecked - the Enabled Property of the second page needs to be set back to "False".

I know how to change the properties of the objects, but what I need help with is the logic of evaluating the current state of all the checkboxes.

Any advice? Tips? Sample Code?

Thanks in advance.

 

[EducatedFool]

Try

Private Sub CheckBox1_Click(): CurrentState: End Sub
Private Sub CheckBox2_Click(): CurrentState: End Sub
Private Sub CheckBox3_Click(): CurrentState: End Sub
Private Sub CheckBox4_Click(): CurrentState: End Sub
Private Sub CheckBox5_Click(): CurrentState: End Sub
Private Sub CheckBox6_Click(): CurrentState: End Sub

Sub [B]CurrentState[/B]()
    Dim CHB_count As Integer, CHB_value As Integer

    For Each contr In [B]Me.MultiPage1.Pages(0).Controls[/B]
        If TypeOf contr Is MSForms.CheckBox Then
            CHB_count = CHB_count + 1: CHB_value = CHB_value - contr.Value
        End If
    Next contr
    
    [B][COLOR="Navy"]Me.MultiPage1.Pages(1).Enabled = CHB_count = CHB_value[/COLOR][/B]
End Sub

 

[Chris The Rock]

Try

Private Sub CheckBox1_Click(): CurrentState: End Sub
Private Sub CheckBox2_Click(): CurrentState: End Sub
Private Sub CheckBox3_Click(): CurrentState: End Sub
Private Sub CheckBox4_Click(): CurrentState: End Sub
Private Sub CheckBox5_Click(): CurrentState: End Sub
Private Sub CheckBox6_Click(): CurrentState: End Sub

Sub [B]CurrentState[/B]()
    Dim CHB_count As Integer, CHB_value As Integer

    For Each contr In [B]Me.MultiPage1.Pages(0).Controls[/B]
        If TypeOf contr Is MSForms.CheckBox Then
            CHB_count = CHB_count + 1: CHB_value = CHB_value - contr.Value
        End If
    Next contr
    
    [B][COLOR="Navy"]Me.MultiPage1.Pages(1).Enabled = CHB_count = CHB_value[/COLOR][/B]
End Sub

There is some syntax in your reply that I've never seen before, but I understand it. I will give it a try tomorrow morning.

Thanks for what looks like a step in the right direction.

 

[Chris The Rock]

By way of follow up, this code you provided worked perfectly for me. I appreciate the time you took to answer my question.

 

[Norie]

Here's another approach, using a class module.

Create a class module (Insert>Class), name it CCheckBoxes and add this code.

Option Explicit
 
Public WithEvents CheckGroup As MSForms.CheckBox
Private Sub CheckGroup_Click()
Dim ctl As MSForms.Control
Dim bl As Boolean
 
    bl = True
 
    For Each ctl In UserForm1.MultiPage1.Pages(0).Controls
        If TypeName(ctl) = "CheckBox" Then
            bl = bl And ctl.Value
        End If
    Next ctl
    UserForm1.MultiPage1.Pages(1).Enabled = bl
 
End Sub

Put this in the userform module.

Option Explicit
 
Dim CheckGrp() As New CCheckBoxes
Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim I As Long
    For Each ctl In Me.MultiPage1.Pages(0).Controls
        If TypeName(ctl) = "CheckBox" Then
             ReDim Preserve CheckGrp(I)
            Set CheckGrp(I).CheckGroup = ctl
            I = I + 1
        End If
 
    Next ctl
End Sub

 

[Chris The Rock]

Here's another approach, using a class module.

Create a class module (Insert>Class), name it CCheckBoxes and add this code.

Option Explicit
 
Public WithEvents CheckGroup As MSForms.CheckBox
Private Sub CheckGroup_Click()
Dim ctl As MSForms.Control
Dim bl As Boolean
 
    bl = True
 
    For Each ctl In UserForm1.MultiPage1.Pages(0).Controls
        If TypeName(ctl) = "CheckBox" Then
            bl = bl And ctl.Value
        End If
    Next ctl
    UserForm1.MultiPage1.Pages(1).Enabled = bl
 
End Sub

Put this in the userform module.

Option Explicit
 
Dim CheckGrp() As New CCheckBoxes
Private Sub UserForm_Initialize()
Dim ctl As MSForms.Control
Dim I As Long
    For Each ctl In Me.MultiPage1.Pages(0).Controls
        If TypeName(ctl) = "CheckBox" Then
             ReDim Preserve CheckGrp(I)
            Set CheckGrp(I).CheckGroup = ctl
            I = I + 1
        End If
 
    Next ctl
End Sub

Ahhh....this looks like the method I was trying to figure out on my own, but couldn't put the pieces together.

Thanks for another demonstration of how to do this! I'm gonna break out my VBA book and read about Class Modules.

 

[Norie]

Chris

Took me some time to get my head round this sort of thing, and I still need to rely on examples I created earlier - I've even got a folder for them.

And I'm still uncertain if I'm doing things correctly, but they do seem to work - eventually.