checkbox to check automatically IF any of other checkboxes are checked

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
457
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
So here is a brief snippet of my userform i have to use as an example:
o0arfn.jpg


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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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
 
Upvote 0
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
icon14.png

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
 
Upvote 0
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
 
Upvote 0
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)
20f8a5x.jpg
 
Last edited:
Upvote 0
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:
Upvote 0
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.
icon14.png


And a fine 'tip of the cap' to you, kind sir!! :LOL:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top