Page 4 of 5 FirstFirst ... 2345 LastLast
Results 31 to 40 of 45

Thread: Userform Checkboxes - Select All/Unselect

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

    Default Re: Userform Checkboxes - Select All/Unselect

    Code:
    Why do we want to Move all to hidden and Move all to visible
    Because if a user just wants to compare 3 competitors against each other, it would be annoying to have to double click each list item to hide them. A user would want to hide them all, then only click the items they want to be visible.
    0 0
     

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

    Default Re: Userform Checkboxes - Select All/Unselect

    So you tell me.

    What do you want to do?
    Are you now wanting to use the ListBox Ideal?
    And you want all the values in Row(7) loaded into both Listboxes when the userform is Opened?

    If this is true then what else do you need?

    You said:
    A user would want to hide them all, then only click the items they want to be visible.

    Do you mean hide all the sheet column or hide all the items in the ListBox
    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"
    1 0
     

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

    Default Re: Userform Checkboxes - Select All/Unselect

    The listbox idea was great but since NoSparks solved the checkbox situation, I believe I'm all set now. I will stick with the code that NoSparks provided.

    Thank you very much for all your help and commitment.
    0 0
     

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

    Default Re: Userform Checkboxes - Select All/Unselect

    Well in case you wanted to see my plan here is the Code you need to put into your Userform
    Just in case you want to see how it works create a New Userform and install:
    One ListBox named: ListBoxHidden
    One ListBox named: ListBoxVisible
    One Command Button named:ToggleVisible

    Now with my code when you open up your UserForm The listBox named :ListBoxVisible
    You willsee a list of all the Columns that are Visible.
    The Listbox named :ListBoxHidden
    You will have see a list of all the hidden columns

    Now if you want to hide a column just double click on the column Name in ListBox named ListBoxVisable
    Now if you want to show a column just double click on the column Name in ListBox named ListBoxHidden
    The two listboxes will update at that time.
    Now if you wanted to hide all the columns or unhide all the columns with just one press of a button:
    Install a Button named ToggleVisible
    And you will see the code below has all the code you need.

    Code:
    Private Sub ToggleVisible_Click()
    Columns(4).Resize(, 37).Hidden = Not Columns(4).Resize(, 37).Hidden
    UpDate_List
    End Sub
    Private Sub ListboxHidden_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Application.ScreenUpdating = False
    On Error GoTo M
    Cancel = True
    Dim c As Long
    Dim i As Long
    Cells(7, 4).Resize(, 37).Select
        Selection.Find(What:=ListBoxHidden.Value, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
            c = ActiveCell.Column
            Columns(c).Hidden = False
            
    For i = 0 To ListBoxHidden.ListCount - 1
        If ListBoxHidden.Selected(i) Then
            ListBoxHidden.RemoveItem (i)
        End If
    Next i
    ListBoxHidden.ListIndex = -1
    Call UpDate_List
    Range("B1").Select
    Application.ScreenUpdating = True
    Exit Sub
    M:
    MsgBox "No value selected"
    End Sub
    Private Sub ListboxVisible_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Application.ScreenUpdating = False
    On Error GoTo M
    Cancel = True
    Dim c As Long
    Dim i As Long
    Cells(7, 4).Resize(, 37).Select
        Selection.Find(What:=ListBoxVisible.Value, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
            c = ActiveCell.Column
            Columns(c).Hidden = True
            
    For i = 0 To ListBoxVisible.ListCount - 1
        If ListBoxVisible.Selected(i) Then
            ListBoxVisible.RemoveItem (i)
        End If
    Next i
    ListBoxVisible.ListIndex = -1
    Call UpDate_List
    Range("B1").Select
    Application.ScreenUpdating = True
    Exit Sub
    M:
    MsgBox "No value selected"
    End Sub
    
    Private Sub UserForm_Initialize()
    For i = 4 To 40
        If Columns(i).Hidden = True Then ListBoxHidden.AddItem Cells(7, i).Value
        If Columns(i).Hidden = False Then ListBoxVisible.AddItem Cells(7, i).Value
    Next
    ListBoxHidden.ControlTipText = "Double Click on me to show this Clients column"
    ListBoxVisible.ControlTipText = "Double Click on me to Hide this Clients column"
    
    End Sub
    Sub UpDate_List()
    ListBoxHidden.Clear
    ListBoxVisible.Clear
    For i = 4 To 40
        If Columns(i).Hidden = True Then ListBoxHidden.AddItem Cells(7, i).Value
        If Columns(i).Hidden = False Then ListBoxVisible.AddItem Cells(7, i).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"
    1 1
     

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

    Default Re: Userform Checkboxes - Select All/Unselect

    That's really awesome! Now I'm conflicted with which method I like better! Thank you for doing this, My Aswer Is This.
    0 0
     

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

    Default Re: Userform Checkboxes - Select All/Unselect

    Well in case you wanted to see my plan here is the Code you need to put into your Userform


    I found a way to incorporate both methods. While playing around with your code, My Aswer is This, I found a flaw that I was hoping could be solve

    If one of the competitor names has the same beginning as another, then the userform gets confused and moves the wrong thing.

    Example:
    If I have a competitor called "Applepie" then I add a competitor called "Apple", there will be an issue.
    When I double-click "Apple" to move it between listboxes (hidden or visible; it doesn't matter), "Applepie" moves instead and then I am no longer able to double click "Apple" to move it anywhere

    Is there a way to fix this?
    0 0
     

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

    Default Re: Userform Checkboxes - Select All/Unselect

    Replace these two Double click scripts:

    Code:
    Private Sub ListboxHidden_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    'Modified  10/14/2019  4:24:10 PM  EDT
    Application.ScreenUpdating = False
    On Error GoTo M
    Cancel = True
    Dim c As Long
    Dim i As Long
    Cells(7, 4).Resize(, 37).Select
        Selection.Find(What:=ListBoxHidden.Value, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
            c = ActiveCell.Column
            Columns(c).Hidden = False
            
    For i = 0 To ListBoxHidden.ListCount - 1
        If ListBoxHidden.Selected(i) Then
            ListBoxHidden.RemoveItem (i)
        End If
    Next i
    ListBoxHidden.ListIndex = -1
    Call UpDate_List
    Range("B1").Select
    Application.ScreenUpdating = True
    Exit Sub
    M:
    MsgBox "No value selected"
    End Sub
    Private Sub ListboxVisible_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    'Modified  10/14/2019  4:24:10 PM  EDT
    Application.ScreenUpdating = False
    On Error GoTo M
    Cancel = True
    Dim c As Long
    Dim i As Long
    Cells(7, 4).Resize(, 37).Select
        Selection.Find(What:=ListBoxVisible.Value, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
            c = ActiveCell.Column
            Columns(c).Hidden = True
            
    For i = 0 To ListBoxVisible.ListCount - 1
        If ListBoxVisible.Selected(i) Then
            ListBoxVisible.RemoveItem (i)
        End If
    Next i
    ListBoxVisible.ListIndex = -1
    Call UpDate_List
    Range("B1").Select
    Application.ScreenUpdating = True
    Exit Sub
    M:
    MsgBox "No value selected"
    End Sub

    If you look close you will see the change I made.

    I changed Part to Whole.
    Meaning it looks at the whole word not just part of word.


    Sorry I did not notice this in my testing.
    Last edited by My Aswer Is This; Oct 14th, 2019 at 04:38 PM.
    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
     

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

    Default Re: Userform Checkboxes - Select All/Unselect

    Hi My Aswer Is This,

    I had someone make a suggestion to my spreadsheet to your userform design and I wanted to know if it would be possible. The idea is this:

    The userform shows one listbox for columns (Competitors) that are visible and one listbox for columns (Competitors) that are hidden.
    Could you create a way to move the competitor names around in the visible columns listbox so that the order will change when the competitors are showing?

    Right now, the listbox is in the order of the header order (which is alphabetically), but what if I want to rearrange the Competitor names in the visible listbox so that the order in which they appear on the sheets mimics the order that I arranged them in on the Userform.

    Can this be done?
    0 0
     

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

    Default Re: Userform Checkboxes - Select All/Unselect

    You said:
    mimics the order that I arranged them in on the Userform.

    How do you plan to arrange them?

    You want to some how arrange them by dragging them around in the userform ?
    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. #40
    Board Regular
    Join Date
    Aug 2019
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Userform Checkboxes - Select All/Unselect

    How do you plan to arrange them?
    I guess whatever way will work. I can think of two intuitive ways to accomplish this:

    1. Click and drag approach - A user would click and drag Competitors to rearrange them in the visible listbox.
    2. "Move up" and "Move down" command - This could be two buttons next to the listbox that allow a user to click and select a option in the listbox, then click the "Move up" command button to push the selected competitor up in the list, or click the "Move down" command button to push the selected competitor down in the list.

    Do you think this can be added somehow?
    Last edited by gaudrco; Oct 18th, 2019 at 01:15 PM.
    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
  •