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

Chris The Rock

Active Member
Joined
Feb 24, 2002
Messages
287
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

EducatedFool

Active Member
Joined
Jun 10, 2009
Messages
272
Try

Code:
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

Active Member
Joined
Feb 24, 2002
Messages
287
Try

Code:
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

Active Member
Joined
Feb 24, 2002
Messages
287
By way of follow up, this code you provided worked perfectly for me. I appreciate the time you took to answer my question.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here's another approach, using a class module.

Create a class module (Insert>Class), name it CCheckBoxes and add this code.
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.
Code:
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

Active Member
Joined
Feb 24, 2002
Messages
287
Here's another approach, using a class module.

Create a class module (Insert>Class), name it CCheckBoxes and add this code.
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.
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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.:oops:
 

Forum statistics

Threads
1,136,334
Messages
5,675,177
Members
419,552
Latest member
jsanjur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top