Userform Checkboxes - Select All/Unselect

Status
Not open for further replies.

jroo

Board Regular
Joined
May 22, 2003
Messages
157
Hi - I'm creating a userform where I'd like to have about 10 checkboxes. How would I go about creating another check box that would select/unselect those 10 boxes?

I tried googling it and came across this piece of code below, but it only selects all. It doesn't allow me to unselect all. Please help. Thanks


Private Sub SelectAllCheckBox_Click()

Dim oCtrl As Control
For Each oCtrl In Me.Controls


If TypeOf oCtrl Is msforms.CheckBox Then
oCtrl.Value = True
End If
Next

End Sub
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,351
Code:
Private Sub SelectAllCheckBox_Click()

Dim oCtrl As Control

For Each oCtrl In Me.Controls
    If TypeOf oCtrl Is msforms.CheckBox Then
        oCtrl.Value = SelectAllCheckBox.Value
    End If
Next

End Sub
 

jroo

Board Regular
Joined
May 22, 2003
Messages
157
Thanks!

How would I go about grouping checkboxes on a userform with a select/unselect check box for each group?

For example if I have 40 boxes on one userform, 10 boxes in 4 groups... Would you be able to give me some sample code to handle that? Thanks so much... I really appreciate it!
 

jroo

Board Regular
Joined
May 22, 2003
Messages
157
Thanks again for your help...

I was able to do what I wanted by grouping the checkboxes with a frame and using the code below

Private Sub SelectAllCsvCheckBox_Click()
Dim oCtrl As Control
For Each oCtrl In Me.CsvFrame.Controls
If TypeOf oCtrl Is msforms.CheckBox Then
oCtrl.Value = SelectAllCsvCheckBox.Value
End If
Next
End Sub

'*********

Now here's another thing I'd like to accomplish

I have the code below that selects checkboxes from various checkboxes across different groups. How could I modify this code so that it unselects the same set of checkboxes? I tried writing code with an array, but the syntax was wrong

Private Sub Reg3CheckBox_Click()

Me.EditsCheckBox = True
Me.SepFreezeCheckBox = True
Me.YoyCheckBox = True
Me.P36s1CheckBox = True
Me.P69A1CheckBox = True
Me.CsvpCheckBox = True

End Sub

'This was my attempt to use 2 of the checkboxes above...didn't work
'Private Sub Reg3CheckBox_Click()
'
' Dim oCtrl As Control
' For Each oCtrl In Array("EditsCheckBox", "SepFreezeCheckBox")
' oCtrl.Value = Reg3CheckBox.Value
' Next
'
'End Sub
 

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
Code:
Private Sub SelectAllCheckBox_Click()

Dim oCtrl As Control

For Each oCtrl In Me.Controls
    If TypeOf oCtrl Is msforms.CheckBox Then
        oCtrl.Value = SelectAllCheckBox.Value
    End If
Next

End Sub
This doesn't seem to be working for me. I am using a userform with many checkboxes that look like this (three examples)
Code:
Private Sub CheckBox1_Click()
Dim xAddress As String
xAddress = "D"
If UserForm1.CheckBox1.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    UserForm1.CheckBox1.Caption = Range("D7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    UserForm1.CheckBox1.Caption = Range("D7").Value
End If
End Sub


Private Sub CheckBox2_Click()
Dim xAddress As String
xAddress = "E"
If UserForm1.CheckBox2.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    UserForm1.CheckBox2.Caption = Range("E7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    UserForm1.CheckBox2.Caption = Range("E7").Value
End If
End Sub


Private Sub CheckBox3_Click()
Dim xAddress As String
xAddress = "F"
If UserForm1.CheckBox3.Value Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    UserForm1.CheckBox3.Caption = Range("F7").Value
Else
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    UserForm1.CheckBox3.Caption = Range("F7").Value
End If
End Sub
The checkbox that I'm using for the select/deselect all is behaving odd. It seems that is selects/deselects the first checkbox fine but it does not work for the others. I've also tried this code and got the same results:
Code:
Private Sub SelectAll_Click()
If UserForm1.Controls("SelectAll").Value = True Then
        For i = 1 To 35
            UserForm1.Controls("Checkbox" & i).Value = True
        Next i
    Else
        For i = 1 To 35
            UserForm1.Controls("Checkbox" & i).Value = False
        Next i
    End If
End Sub
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,351
Have you tried moving all your checkbox Click events into the respective Change events?
 

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
I just tried that and it did not change anything. Its still behaving the same way.
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,743
Office Version
2013
Platform
Windows
Try this:
Assuming the name of the Checkbox is CheckBox1 Which you want to put your code in.

This script will set all Check Boxes to the same value as CheckBox1.
Code:
Private Sub CheckBox1_Click()
'Modified  10/7/2019  9:22:15 AM  EDT
For Each Control In Me.Controls
    If TypeName(Control) = "CheckBox" Then Control.Value = CheckBox1.Value
Next
End Sub
 

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
Try this:
Assuming the name of the Checkbox is CheckBox1 Which you want to put your code in.

This script will set all Check Boxes to the same value as CheckBox1.
Code:
Private Sub CheckBox1_Click()
'Modified  10/7/2019  9:22:15 AM  EDT
For Each Control In Me.Controls
    If TypeName(Control) = "CheckBox" Then Control.Value = CheckBox1.Value
Next
End Sub
This seems to be working but I don't understand why.

Checkbox38 has this code:
Code:
Private Sub CheckBox38_Click()Dim oCtrl As Control


For Each oCtrl In Me.Controls
    If TypeOf oCtrl Is msforms.Checkbox Then
        oCtrl.Value = CheckBox38.Value
    End If
Next


End Sub
And Checkbox37 has your code. Everytime I click Checkbox38 to Select/Deselect all, Checkbox37 is included. Does it have to be? I don't understand
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,096,321
Messages
5,449,704
Members
405,575
Latest member
Masimo85

This Week's Hot Topics

Top