Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 42

Thread: Userform Checkboxes - Select All/Unselect

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

    Default Re: Userform Checkboxes - Select All/Unselect

    You asked is there a easier way.

    Why not have the user double click A1 to Hide column A
    Or Double click B1 to hide column B

    Then if you want to unhide all columns Double click a certain cell.

    Would something like this work?
    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"

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

    Default Re: Userform Checkboxes - Select All/Unselect

    Thank you for your help and suggestions. I appreciate it

    The issue I see with that method for my situation is it's not clear what columns would be hidden. With the userform method, a user could click the 'Show userform' button to launch the userform, and would be able to easily see what is hidden. The columns headers are competitor names so its more important to know which competitors are hidden as oppose to with column letter (Column D, Column G, etc,). I want the users to be able to clearly see what competitors are showing and what competitors are hidden.

    Your code was working fine until I added ControlSources to the checkboxes. The ControlSources saves the checkbox values to a column in my spreadsheet so that when I close and open the userform, the checkboxes values remain as the last value it was.

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

    Default Re: Userform Checkboxes - Select All/Unselect

    I would like to see the code your using.

    You said:
    a user could click the 'Show userform' button to launch the userform, and would be able to easily see what is hidden.

    How can a user see what is hidden?

    I would have to look into Row Source I hardly ever use it.

    What is being displayed on the userform which shows which columns are hidden.

    I do not mean to ask too many questions but I'm not sure how things are working.

    So if user selects Checkbox 1 is column 1 hidden?

    And are the CheckBoxes named
    competitor names?

    So if checkbox named Alpha is selected column Header with value Alpha is hidden.

    So user knows which columns are hidden by looking at checkbox

    Is this how things work?





    Last edited by My Aswer Is This; Oct 8th, 2019 at 10:00 AM.
    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"

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

    Default Re: Userform Checkboxes - Select All/Unselect

    Here is my code to the checkboxes:
    Code:
    Private Sub CheckBox1_Change()
    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_Change()
    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_Change()
    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
    
    
    
    
    Private Sub CheckBox4_Change()
    Dim xAddress As String
    xAddress = "G"
    If UserForm1.CheckBox4.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox4.Caption = Range("G7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox4.Caption = Range("G7").Value
    End If
    End Sub
    
    
    
    
    Private Sub CheckBox5_Change()
    Dim xAddress As String
    xAddress = "H"
    If UserForm1.CheckBox5.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox5.Caption = Range("H7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox5.Caption = Range("H7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox6_Change()
    Dim xAddress As String
    xAddress = "I"
    If UserForm1.CheckBox6.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox6.Caption = Range("I7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox6.Caption = Range("I7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox7_Change()
    Dim xAddress As String
    xAddress = "J"
    If UserForm1.CheckBox7.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox7.Caption = Range("J7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox7.Caption = Range("J7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox8_Change()
    Dim xAddress As String
    xAddress = "K"
    If UserForm1.CheckBox8.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox8.Caption = Range("K7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox8.Caption = Range("K7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox9_Change()
    Dim xAddress As String
    xAddress = "L"
    If UserForm1.CheckBox9.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox9.Caption = Range("L7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox9.Caption = Range("L7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox10_Change()
    Dim xAddress As String
    xAddress = "M"
    If UserForm1.CheckBox10.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox10.Caption = Range("M7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox10.Caption = Range("M7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox11_Change()
    Dim xAddress As String
    xAddress = "N"
    If UserForm1.CheckBox11.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox11.Caption = Range("N7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox11.Caption = Range("N7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox12_Change()
    Dim xAddress As String
    xAddress = "O"
    If UserForm1.CheckBox12.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox12.Caption = Range("O7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox12.Caption = Range("O7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox13_Change()
    Dim xAddress As String
    xAddress = "P"
    If UserForm1.CheckBox13.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox13.Caption = Range("P7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox13.Caption = Range("P7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox14_Change()
    Dim xAddress As String
    xAddress = "Q"
    If UserForm1.CheckBox14.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox14.Caption = Range("Q7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox14.Caption = Range("Q7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox15_Change()
    Dim xAddress As String
    xAddress = "R"
    If UserForm1.CheckBox15.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox15.Caption = Range("R7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox15.Caption = Range("R7").Value
    End If
    End Sub
    Private Sub CheckBox16_Change()
    Dim xAddress As String
    xAddress = "S"
    If UserForm1.CheckBox16.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox16.Caption = Range("S7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox16.Caption = Range("S7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox17_Change()
    Dim xAddress As String
    xAddress = "T"
    If UserForm1.CheckBox17.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox17.Caption = Range("T7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox17.Caption = Range("T7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox18_Change()
    Dim xAddress As String
    xAddress = "U"
    If UserForm1.CheckBox18.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox18.Caption = Range("U7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox18.Caption = Range("U7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox19_Change()
    Dim xAddress As String
    xAddress = "V"
    If UserForm1.CheckBox19.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox19.Caption = Range("V7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox19.Caption = Range("V7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox20_Change()
    Dim xAddress As String
    xAddress = "W"
    If UserForm1.CheckBox20.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox20.Caption = Range("W7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox20.Caption = Range("W7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox21_Change()
    Dim xAddress As String
    xAddress = "X"
    If UserForm1.CheckBox21.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox21.Caption = Range("X7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox21.Caption = Range("X7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox22_Change()
    Dim xAddress As String
    xAddress = "Y"
    If UserForm1.CheckBox22.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox22.Caption = Range("Y7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox22.Caption = Range("Y7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox23_Change()
    Dim xAddress As String
    xAddress = "Z"
    If UserForm1.CheckBox23.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox23.Caption = Range("Z7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox23.Caption = Range("Z7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox24_Change()
    Dim xAddress As String
    xAddress = "AA"
    If UserForm1.CheckBox24.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox24.Caption = Range("AA7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox24.Caption = Range("AA7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox25_Change()
    Dim xAddress As String
    xAddress = "AB"
    If UserForm1.CheckBox25.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox25.Caption = Range("AB7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox25.Caption = Range("AB7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox26_Change()
    Dim xAddress As String
    xAddress = "AC"
    If UserForm1.CheckBox26.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox26.Caption = Range("AC7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox26.Caption = Range("AC7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox27_Change()
    Dim xAddress As String
    xAddress = "AD"
    If UserForm1.CheckBox27.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox27.Caption = Range("AD7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox27.Caption = Range("AD7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox28_Change()
    Dim xAddress As String
    xAddress = "AE"
    If UserForm1.CheckBox28.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox28.Caption = Range("AE7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox28.Caption = Range("AE7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox29_Change()
    Dim xAddress As String
    xAddress = "AF"
    If UserForm1.CheckBox29.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox29.Caption = Range("AF7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox29.Caption = Range("AF7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox30_Change()
    Dim xAddress As String
    xAddress = "AG"
    If UserForm1.CheckBox30.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox30.Caption = Range("AG7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox30.Caption = Range("AG7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox31_Change()
    Dim xAddress As String
    xAddress = "AH"
    If UserForm1.CheckBox31.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox31.Caption = Range("AH7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox31.Caption = Range("AH7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox32_Change()
    Dim xAddress As String
    xAddress = "AI"
    If UserForm1.CheckBox32.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox32.Caption = Range("AI7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox32.Caption = Range("AI7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox33_Change()
    Dim xAddress As String
    xAddress = "AJ"
    If UserForm1.CheckBox33.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox33.Caption = Range("AJ7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox33.Caption = Range("AJ7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox34_Change()
    Dim xAddress As String
    xAddress = "AK"
    If UserForm1.CheckBox34.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox34.Caption = Range("AK7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox34.Caption = Range("AK7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox35_Change()
    Dim xAddress As String
    xAddress = "AL"
    If UserForm1.CheckBox35.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox35.Caption = Range("AL7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox35.Caption = Range("AL7").Value
    End If
    End Sub
    Private Sub CheckBox36_Change()
    Dim xAddress As String
    xAddress = "AM"
    If UserForm1.CheckBox36.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox36.Caption = Range("AM7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox36.Caption = Range("AM7").Value
    End If
    End Sub
    
    
    Private Sub CheckBox37_Change()
    Dim xAddress As String
    xAddress = "AN"
    If UserForm1.CheckBox37.Value Then
        Application.ActiveSheet.Columns(xAddress).Hidden = False
        UserForm1.CheckBox37.Caption = Range("AN7").Value
    Else
        Application.ActiveSheet.Columns(xAddress).Hidden = True
        UserForm1.CheckBox37.Caption = Range("AN7").Value
    End If
    End Sub
    
    'This is suppose to be the Select/Deselect All Checkbox
    Private Sub CheckBox38_Click()
    For Each Control In Me.Controls
        If TypeName(Control) = "CheckBox" Then Control.Value = CheckBox38.Value
    Next
    The name of each Checkbox 1- 37 is a competitor name. So when the userform is opened, the users can see the competitor names and which ones are showing and which ones are hiding. I am using this code to call out the names of the competitors, by cell reference, to establish the name of each checkbox when the userform is initially opened.
    Code:
    Private Sub UserForm_Activate()
    UserForm1.CheckBox1.Caption = Range("D7").Value
    UserForm1.CheckBox2.Caption = Range("E7").Value
    UserForm1.CheckBox3.Caption = Range("F7").Value
    UserForm1.CheckBox4.Caption = Range("G7").Value
    UserForm1.CheckBox5.Caption = Range("H7").Value
    UserForm1.CheckBox6.Caption = Range("I7").Value
    UserForm1.CheckBox7.Caption = Range("J7").Value
    UserForm1.CheckBox8.Caption = Range("K7").Value
    UserForm1.CheckBox9.Caption = Range("L7").Value
    UserForm1.CheckBox10.Caption = Range("M7").Value
    UserForm1.CheckBox11.Caption = Range("N7").Value
    UserForm1.CheckBox12.Caption = Range("O7").Value
    UserForm1.CheckBox13.Caption = Range("P7").Value
    UserForm1.CheckBox14.Caption = Range("Q7").Value
    UserForm1.CheckBox15.Caption = Range("R7").Value
    UserForm1.CheckBox16.Caption = Range("S7").Value
    UserForm1.CheckBox17.Caption = Range("T7").Value
    UserForm1.CheckBox18.Caption = Range("U7").Value
    UserForm1.CheckBox19.Caption = Range("V7").Value
    UserForm1.CheckBox20.Caption = Range("W7").Value
    UserForm1.CheckBox21.Caption = Range("X7").Value
    UserForm1.CheckBox22.Caption = Range("Y7").Value
    UserForm1.CheckBox23.Caption = Range("Z7").Value
    UserForm1.CheckBox24.Caption = Range("AA7").Value
    UserForm1.CheckBox25.Caption = Range("AB7").Value
    UserForm1.CheckBox26.Caption = Range("AC7").Value
    UserForm1.CheckBox27.Caption = Range("AD7").Value
    UserForm1.CheckBox28.Caption = Range("AE7").Value
    UserForm1.CheckBox29.Caption = Range("AF7").Value
    UserForm1.CheckBox30.Caption = Range("AG7").Value
    UserForm1.CheckBox31.Caption = Range("AH7").Value
    UserForm1.CheckBox32.Caption = Range("AI7").Value
    UserForm1.CheckBox33.Caption = Range("AJ7").Value
    UserForm1.CheckBox34.Caption = Range("AK7").Value
    UserForm1.CheckBox35.Caption = Range("AL7").Value
    UserForm1.CheckBox36.Caption = Range("AM7").Value
    UserForm1.CheckBox37.Caption = Range("AN7").Value
    End sub
    The column that corresponds to each checkbox is different.
    CheckBox1 does NOT equal Column A;

    CheckBox1 will show/hide Column D
    CheckBox2 will show/hide Column E
    CheckBox3 will show/hide Column F
    CheckBox4 will show/hide Column G
    .... and so on

    So if checkbox named Alpha is selected column Header with value Alpha is hidden.

    So user knows which columns are hidden by looking at checkbox

    Is this how things work?
    This is correct.

    I hope that answered all your question but if I missed anything, let me know.

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

    Default Re: Userform Checkboxes - Select All/Unselect

    I have another suggestion.

    I have written a script that would work this way.

    On your Userform you only need two Listboxes

    One listbox shows all the hidden column Headers
    One Listbox shows all the Visible column Headers

    But now after looking at your script the Header is actually in Row(7) of the active sheet.


    So the script would load all these headers into the two listboxes when you activate the Userform

    Now if you double click on ListBox1 which shows the Visible column headers that column will now be hidden and added to the list in listbox2 which is a list of all the hidden columns.

    And the other way around also.




    And we could have it to where if you double click on a value in Listbox2 that column would now be hidden.


    Doing it this way would only require two listboxes and very little code.

    And this happens automatically when you activate the Userform.

    Next Time you open the Userform the two listboxes will show all the hidden columns and all the Visible columns.

    Would you be interested in this approach.

    My thinking is using 38 CheckBoxes and all this code your needing is a very hard way to do this.

    My ideal only requires about a little bit of code.
    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"

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

    Default Re: Userform Checkboxes - Select All/Unselect

    Thank you

    Yes I am interested in doing this. Would there be a way to "Move all to hidden" and "Move all to visible"?

  7. #27
    Board Regular
    Join Date
    Mar 2013
    Posts
    805
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    Staying with the code of post 24
    try changing userform activate to
    Code:
    Private Sub UserForm_Activate()
        Dim arr As Variant
        Dim i As Long
    arr = Application.Transpose(Range("D7:AN7").Value)
    For i = 1 To 37
        With Controls("CheckBox" & i)
            .Caption = arr(i, 1)
            .Value = Not Columns(i + 3).Hidden
        End With
    Next i
    End Sub
    and the checkbox _change subs to be like this
    Code:
    Private Sub CheckBox1_Change()
    Dim xAddress As String
    xAddress = "D"
        Application.ActiveSheet.Columns(xAddress).Hidden = Not CheckBox1.Value
    End Sub
    Leave checkbox38 code as is.

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

    Default Re: Userform Checkboxes - Select All/Unselect

    NoSparks I don't know how you do it! This worked!

    How does it save the checkbox configuration after the userform is closed and opened again? I don't understand how you made that happen

  9. #29
    Board Regular
    Join Date
    Mar 2013
    Posts
    805
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    How does it save the checkbox configuration after the userform is closed and opened again?
    It doesn't save anything.
    It starts all over from square one every time the form is activated.
    The headers (D7 to AN7) populate the array and are used for the check boxes captions.
    Whether the checkbox should be checked or not is determined by that column already being hidden or not.

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

    Default Re: Userform Checkboxes - Select All/Unselect

    In post 26 you said:

    Yes I am interested in doing this. Would there be a way to "Move all to hidden" and "Move all to visible"?

    Why do we want to Move all to hidden and Move all to visible

    When the UserForm is opened my plan was to show all the hidden column headers in Listbox named Hidden and Load all the visible column headers would be shown in ListBox named visible.

    This way you do not need any row source.
    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"

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
  •