Listbox with "Select All"

[Jaymond Flurrie]

I would need a simple list box (say, three items), and under it a "select all" checkbox and when this is clicked all three items are selected and when this checkbox is clicked again, all selections are removed. Otherwise this is easy, but I want it to be clear for user, so when the checkbox is true and user selects something, this check has to disappear. I have tried pretty much everything, but can't seem to get it to work, that change-event of listbox prevents me from getting this done.

 

[Tom Urtis]

You say you want your listbox "to be clear for user" but you want to have a checkbox with a caption "select all" that when you first click it, it selects all items, but when you click the "select all" checkbox again it deselects everything. Deselecting all items when you click a "select all" checkbox is not clear to the user.

You said you only have 3 items in a listbox. Load the listbox with the first 2 items being Select all and Deselect all. That will be a total of 5 items in the listbox, still a small listbox with all 5 items showing, all compartmentalized in the single listbox and more intuitive.

BTW, it always helps if you specify what kind of listbox you are talking about (Forms or ActiveX), and if ActiveX, whether embedded onto a worksheet or as a control on a userform.

 

[Jaymond Flurrie]

I think just being able to select everything (the real list I use has 10 items, but 12 is not a problem) instead of needing to click them all would be clear enough, the problem just is that if I do it with a checkbox, it works right now so that when user deselects something from the list, it has absolutely no effect if the checkbox is still checked.

I'm not sure how to check whether those listboxes I use are ActiveX or not, what's the Excel default? What I can tell is that those forms I use are built with VBA using that "Controls" toolbox, but it doesn't say there which ones those are, just that those are listboxes. And yes, they are embedded into the userform.

 

[Jaymond Flurrie]

Here's my current solution - which naturally doesn't work:

dim mbBoxChange as boolean 'Keep track of where the call came from

Private Sub chkAll_Click()
    'Select everything
    Dim i As Integer
   
    mbBoxChange = True
    
    'Mark all to what the checkbox shows
    For i = 0 To lstListBox.ListCount - 1
        lstListBox.Selected(i) = Me.chkAll
    Next i
   
   Exit Sub
End Sub

Private Sub lstListBox_Change()
    
    Dim i As Integer
    
    If mbBoxChange Then
        Me.chkAll = False
    Else
        With Me.lstListBox
            For i = 0 To .ListCount - 1
                If Not .Selected(i) Then
                    Me.chkAll = False
                    mbBoxChange = False
                    Exit Sub
                End If
            Next i
        End With
    End If
End Sub

 

[Jaymond Flurrie]

One option to solve it would be to turn off the events. Is that possible?

 

[hhh]

Hi,

Can use this code


Private Sub ALL_Click()
Dim r As Integer
For r = 0 To ListBox1.ListCount - 1
ListBox1.Selected(r) = True
Next r

End Sub



Private Sub Non_Click()
Dim r As Integer
For r = 0 To ListBox1.ListCount - 1
ListBox1.Selected(r) = False
Next r
End Sub

 

[Jaymond Flurrie]

Hi,

Can use this code


Private Sub ALL_Click()
Dim r As Integer
For r = 0 To ListBox1.ListCount - 1
ListBox1.Selected(r) = True
Next r

End Sub



Private Sub Non_Click()
Dim r As Integer
For r = 0 To ListBox1.ListCount - 1
ListBox1.Selected(r) = False
Next r
End Sub

But doesn't this do the same thing than what I originally had ie. this later sub is actually "clear all selections" instead of "go ahead and unselect something that you don't want to have selected and while you do that, I'll remove this check from here to avoid confusion"?

 

[Hilles]

Hi.
This is a really old topic but I had this problem today and solved it using a CheckBox with "Select All" caption and the following code:

Private Sub CheckBox1_Change()
Dim N As Single
If CheckBox1.Value = True Then
For N = 0 To ListBox1.ListCount - 1
ListBox1.Selected(N) = True
Next N
Else
For N = 0 To ListBox1.ListCount - 1
ListBox1.Selected(N) = False
Next N
End If
End Sub

 

[PritishS]

Thank you Hilles!! Good and Working Solution!!

Regards,
PritishS

 

[antman2988]

I am so glad I found this. Thank you so much for this solution! I have a listbox with 30 selections, and it's a pain to select each one individually.