Userform Checkboxes - Select All/Unselect

Status
Not open for further replies.

jroo

Board Regular
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
 

mikerickson

MrExcel MVP
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
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
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
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
Have you tried moving all your checkbox Click events into the respective Change events?
 

gaudrco

Board Regular
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
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
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.

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top