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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
I just tried that and it did not change anything. Its still behaving the same way.
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,212,928
Messages
6,110,737
Members
448,295
Latest member
Uzair Tahir Khan

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