checkbox to check automatically IF any of other checkboxes are checked

kbishop94

Board Regular
Joined
Dec 5, 2016
Messages
246
So here is a brief snippet of my userform i have to use as an example:


the top right-hand box (where it says "miscellaneous incidents") has a transparent label over top of the box so it cannot be manually checked... (i put a single frame around it only so that it can be seen for my example here...) that box is named: chkIN

I have the following checkboxes listed below the top one:
chkSUP = "supplier"
chkCON = "contractor"
chkAUD = "audit"
chkPROP = "property"
chkALA = "alarm"
chkRAIL = "railcar"
chkOTH = "other"

I need the top checkbox (chkIN) to become checked if ANY of the other boxes are checked. (and then when nothing is checked within that group of boxes, the chkIN in turn will also become unchecked.)

I have (simple) code throughout my userforms where i can check or uncheck boxes based on whether another box is checked/unchecked... but NOT when it involves multiple checkboxes like I do here.

Here is my simple code I use when I am just using 1 box to change the other box... but I cannot figure out how to use "or" in this so i can include other checkboxes and their status...
Code:
Private Sub chkNo_Change()
If chkNo.Value = True Then chkYes = False
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,721
Office Version
2007
Platform
Windows
Try this:

Code:
Private Sub chkALA_Click()
    Call check_chkIN
End Sub
Private Sub chkAUD_Click()
    Call check_chkIN
End Sub
Private Sub chkCON_Click()
    Call check_chkIN
End Sub
Private Sub chkOTH_Click()
    Call check_chkIN
End Sub
Private Sub chkPROP_Click()
    Call check_chkIN
End Sub
Private Sub chkRAIL_Click()
    Call check_chkIN
End Sub
Private Sub chkSUP_Click()
    Call check_chkIN
End Sub


Sub check_chkIN()
    checks = Array(chkSUP, chkCON, chkAUD, chkPROP, chkALA, chkRAIL, chkOTH)
    For i = 0 To UBound(checks)
        If checks(i).Value = True Then
            chkIN = True
            Exit Sub
        End If
    Next
    chkIN = False
End Sub
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,442
Another approach: try using the following line in all your chkXXXX_Change subs
Code:
chkIN = chkSUP Or chkCON Or chkAUD Or chkPROP Or chkALA Or chkRAIL Or chkOTH
for example
Code:
Private Sub chkSUP_Change()
    chkIN = chkSUP Or chkCON Or chkAUD Or chkPROP Or chkALA Or chkRAIL Or chkOTH
End Sub
 

kbishop94

Board Regular
Joined
Dec 5, 2016
Messages
246
Thank you DanteAmor, this worked just fine.

Not sure if I should start another thread for this or just post it here...(if I am wrong a mod can lock this :eek: )

I would like to limit the number of checkboxes that can be checked to 2 total. (within this group, not counting chkIN, which you already provided code for auto-checking that one

Speaking of groups, would the code be better suited for me if I had these grouped (either using a name or a frame?) ?? I have a total of 4 other 'groups' of textboxes on this userform that all relate to a specific area for each 'group' (I do NOT currently have them grouped in VBA... I am just using that term to describe how they appear on my userform.)

THoughts? Thank you!!

Try this:

Code:
Private Sub chkALA_Click()
    Call check_chkIN
End Sub
Private Sub chkAUD_Click()
    Call check_chkIN
End Sub
Private Sub chkCON_Click()
    Call check_chkIN
End Sub
Private Sub chkOTH_Click()
    Call check_chkIN
End Sub
Private Sub chkPROP_Click()
    Call check_chkIN
End Sub
Private Sub chkRAIL_Click()
    Call check_chkIN
End Sub
Private Sub chkSUP_Click()
    Call check_chkIN
End Sub


Sub check_chkIN()
    checks = Array(chkSUP, chkCON, chkAUD, chkPROP, chkALA, chkRAIL, chkOTH)
    For i = 0 To UBound(checks)
        If checks(i).Value = True Then
            chkIN = True
            Exit Sub
        End If
    Next
    chkIN = False
End Sub
 

kbishop94

Board Regular
Joined
Dec 5, 2016
Messages
246
Thank you, Tetra. THis works as well... please see my additional question regarding if I would be better off grouping these (and 3 other similar groups of checkboxes on my userform) :confused:

Another approach: try using the following line in all your chkXXXX_Change subs
Code:
chkIN = chkSUP Or chkCON Or chkAUD Or chkPROP Or chkALA Or chkRAIL Or chkOTH
for example
Code:
Private Sub chkSUP_Change()
    chkIN = chkSUP Or chkCON Or chkAUD Or chkPROP Or chkALA Or chkRAIL Or chkOTH
End Sub
 

kbishop94

Board Regular
Joined
Dec 5, 2016
Messages
246
Here a more complete screenshot of my userform. You can see the 4 different groups of checkboxes I have. (but NOT grouped using VBA... just "grouped" for the specific area that they all relate to.)

My goal now is to limit the number of checkboxes withing each of those groups to just TWO max. (not counting the top checkbox that now becomes checked whenever any of the lower ones are ticked... ) (thanks DanteAmor & Tetra210)
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,721
Office Version
2007
Platform
Windows
Try this:

Call sub check_groups with 3 arguments, the group number (1,2,3 or 4), the checkbox and the group check

Code:
Private Sub chkALA_Click()
    Call check_groups(1, chkALA, chkIN)
End Sub
Private Sub chkAUD_Click()
    Call check_groups(1, chkAUD, chkIN)
End Sub
Private Sub chkCON_Click()
    Call check_groups(1, chkCON, chkIN)
End Sub
Private Sub chkOTH_Click()
    Call check_groups(1, chkOTH, chkIN)
End Sub
Private Sub chkPROP_Click()
    Call check_groups(1, chkPROP, chkIN)
End Sub
Private Sub chkRAIL_Click()
    Call check_groups(1, chkRAIL, chkIN)
End Sub
Private Sub chkSUP_Click()
    Call check_groups(1, chkSUP, chkIN)
End Sub

'[B]do the same with groups 2,3 and 4[/B]
 
Sub check_groups(grp, checkb As MSForms.CheckBox, checkG As MSForms.CheckBox)
    Dim checks As Variant, i As Long, n As Long
    
    Select Case grp
        Case 1
            checks = Array(chkSUP, chkCON, chkAUD, chkPROP, chkALA, chkRAIL, chkOTH)
        Case 2
            checks = Array(chkleakin, chkOff, chkPerfor, chkConta, chkdoesnt)
        Case 3
            checks = Array(chkprocfai, chkprocinter, chkcontaissue)
    End Select
    For i = 0 To UBound(checks)
        If checks(i).Value = True Then
            n = n + 1
            If n = 3 Then
                MsgBox "Maximum allowed 2"
                checkb = False
            End If
            checkG = True
        End If
    Next
    If n = 0 Then checkG = False
End Sub
 
Last edited:

kbishop94

Board Regular
Joined
Dec 5, 2016
Messages
246
Try this:

Call sub check_groups with 3 arguments, the group number (1,2,3 or 4), the checkbox and the group check
DanteAmor:
Thank you(!) that worked beautifully on the very first try.


And a fine 'tip of the cap' to you, kind sir!! :LOL:
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,442
An alternative approach: try constructing your chkXXXX_Change subs based on the following example
Code:
Private Sub chk[COLOR=#ff0000]SUP[/COLOR]_Change()
    chkIN = chkSUP Or chkCON Or chkAUD Or chkPROP Or chkALA Or chkRAIL Or chkOTH
    If CLng(chkSUP) + CLng(chkCON) + CLng(chkAUD) + CLng(chkPROP) + CLng(chkALA) + CLng(chkRAIL) + CLng(chkOTH) < -2 Then chk[COLOR=#ff0000]SUP[/COLOR] = False
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,721
Office Version
2007
Platform
Windows
DanteAmor:
Thank you(!) that worked beautifully on the very first try.


And a fine 'tip of the cap' to you, kind sir!! :LOL:
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,077,795
Messages
5,336,373
Members
399,077
Latest member
johnk94

Some videos you may like

This Week's Hot Topics

Top