Page 1 of 5 123 ... LastLast
Results 1 to 10 of 45

Thread: Userform Checkboxes - Select All/Unselect

  1. #1
    Board Regular
    Join Date
    May 2003
    Posts
    157
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Userform Checkboxes - Select All/Unselect

    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
    0 0
     

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,644
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    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
    0 0
     

  3. #3
    Board Regular
    Join Date
    May 2003
    Posts
    157
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    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!
    0 0
     

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,644
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    0 0
     

  5. #5
    Board Regular
    Join Date
    May 2003
    Posts
    157
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    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
    0 0
     

  6. #6
    Board Regular
    Join Date
    Aug 2019
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    Quote Originally Posted by mikerickson View Post
    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 by gaudrco; Oct 4th, 2019 at 03:26 PM.
    0 0
     

  7. #7
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,644
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    Have you tried moving all your checkbox Click events into the respective Change events?
    0 0
     

  8. #8
    Board Regular
    Join Date
    Aug 2019
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    I just tried that and it did not change anything. Its still behaving the same way.
    Last edited by gaudrco; Oct 7th, 2019 at 09:39 AM.
    0 0
     

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,884
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"
    0 0
     

  10. #10
    Board Regular
    Join Date
    Aug 2019
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    Quote Originally Posted by My Aswer Is This View Post
    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
    0 0
     

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •