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
 
Code:
[COLOR=#333333][FONT=Verdana]Why do we want to Move all to hidden and Move all to visible[/FONT][/COLOR]

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

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
That's really awesome! Now I'm conflicted with which method I like better! Thank you for doing this, My Aswer Is This.
 
Upvote 0
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?
 
Upvote 0
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:=[COLOR=#ff0000]xlWhole[/COLOR], 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:=[COLOR=#ff0000]xlWhole[/COLOR], 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:
Upvote 0
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?
 
Upvote 0
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 ?
 
Upvote 0
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:
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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