"Select All" code in a ListBox

Eduard · #1 09-08-2008, 07:33 AM

Hi,

I'm trying to get a code for a CheckBox which if selected will select all the values in a multi select ListBox.

Code:

Private Sub CheckBox1_Click()

If CheckBox1 = True Then
For r = 1 To ListBox1.ListCount
ListBox1.???= True
Next

End If

End Sub

Can someone help me to get the correct code to turn the values in the ListBox to True?

Thanks,
Eduard

**dominicb** · #2 09-08-2008, 08:05 AM

Good morning Eduard

Quote:

Originally Posted by Eduard

Can someone help me to get the correct code to turn the values in the ListBox to True?

Something like this should work OK :

Code:

Private Sub CheckBox1_Click()

If CheckBox1 = True Then
For r = 1 To ListBox1.ListCount
ListBox1.Selected(r)= True
Next

End If

End Sub

HTH

DominicB

Eduard · #3 09-08-2008, 08:57 AM

Thanks Dominic. I was missing this Selected(r) code.

On the same subject: how can I turn the CheckBox value to False if at least 1 value in ListBox is False?

Thanks,
Eduard

**dominicb** · #4 09-08-2008, 09:25 AM

Hi Eduard

Quote:

Originally Posted by Eduard

On the same subject: how can I turn the CheckBox value to False if at least 1 value in ListBox is False?

I suppose you could simply loop through the listbox again, and every time an instance of False is found, set CheckBox1 to false. This might be considered inefficient in that it is needlessly accessing the checkbox every time a false is found - rather than just if at least one is false, but for the number of items in the listbox, it saves introducing an extra variable.

Code:

If CheckBox1 = True Then
For r = 1 To ListBox1.ListCount
If ListBox1.Selected(r)= False Then CheckBox1.Value=False
Next

BTW, just spotted my little typo below (now corrected) - well deciphered

HTH

DominicB

**Andy Pope** · #5 09-08-2008, 09:41 AM

You can always exit out of the loop on first occurance of an unselected item

Code:

    If CheckBox1 = True Then
        For r = 0 To ListBox1.ListCount-1
            If ListBox1.Selected(r) = False Then
                CheckBox1.Value = False
                Exit For
            End If
        Next
    End If

Eduard · #6 09-08-2008, 10:21 AM

Thanks for the thoughts guys. Unfortunately neither of the version is working. In Andy's case I can't change the value of the CheckBox to True as long as there is at least on False value in the ListBox

**Andy Pope** · #7 09-08-2008, 10:30 AM

This works for me. Userform with listbox and checkbox

Code:

Private m_blnSelectingAll As Boolean

Private Sub CheckBox1_Click()

    Dim lngIndex As Long
    
    m_blnSelectingAll = True
    If CheckBox1.Value Then
        For lngIndex = 0 To ListBox1.ListCount - 1
            ListBox1.Selected(lngIndex) = True
        Next
    End If
    m_blnSelectingAll = False
    
End Sub

Private Sub ListBox1_Change()

    Dim lngIndex As Long
    
    If CheckBox1.Value Then
        If Not m_blnSelectingAll Then
            For lngIndex = 0 To ListBox1.ListCount - 1
                If Not ListBox1.Selected(lngIndex) Then
                    CheckBox1.Value = False
                    Exit For
                End If
            Next
        End If
    End If
    
End Sub

Private Sub UserForm_Initialize()

    ListBox1.List = Array(1, 2, 3, 4, 5, 6, 7)
    ListBox1.MultiSelect = fmMultiSelectMulti
    
End Sub

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copying from one workbook and pasting into another additon to code	snordr17	Excel Programming	1	06-30-2008 06:39 PM
MouseWheel on Listbox Crashes my Excel 2007	ShredDude	Excel 2007 Help	3	05-09-2008 02:19 AM
Running code while displaying a UserForm with vbModeless	PilgrimTim	Excel Programming	7	08-06-2007 09:08 AM
Excel closes when using VBA to write VBA code	btoback	Excel Programming	1	06-04-2007 04:42 PM
Problems understanding automated emailing code.	DDONNI	Excel Programming	15	01-19-2007 12:26 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)