List Box Woes

[AlexanderBB]

It appears with MultiSelect=2 that the list box click event does not fire.
So I tried the Mouse Up event but Debug.Print ListBox2.Value there prints Null

Is there any way to get the list box value in this situation?

Thanks

 

[humdingaling]

are you after the last selected or all the selected items?

this gives all selected

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    
    Dim i As Long
    Dim theMSG As String
    
    
    For i = 0 To ListBox1.ListCount - 1
        
        If ListBox1.Selected(i) = True Then
            
            theMSG = theMSG & " " & ListBox1.List(i)
            
        End If
    Next
    
    MsgBox theMSG
    
End Sub

this gives just the last selected

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    
If ListBox1.ListIndex >= 0 Then
   If ListBox1.Selected(ListBox1.ListIndex) Then
       MsgBox ListBox1.List(ListBox1.ListIndex)
   End If
End If
End Sub

 

[AlexanderBB]

Thanks for that... works a treat.

I've actually got 3 listboxes side by side but want them all to act as one and let me click - then
shift click to select a range.

I should be able to sort it out now !

 

[mikerickson]

When a ListBox is set to .MultiSelect = fmMultiSelectSingle, the .Value property returns the list item that has been selected.

When a ListBox is set to .MultiSelect either fmMultiSelectMulti (1) or fmMultiSelectExtended (2), the .Value property becomes kind of useless, since there may be many items selected. Plus, its not clear what setting the .Value property should do.

A MultiSelect list box does not fire the Click event, but it does fire the Change event.

If you do want something similar to .Value that will work with a MultiSelect listbox, you can add one.

Property Get ListBox1Value() As String
    Const Delimiter As String = ","
    Dim i As Long
    With ListBox1
        If Not (IsNull(.Value) And .MultiSelect = fmMultiSelectSingle) Then
            For i = 0 To .ListCount - 1
                If .Selected(i) Then
                    ListBox1Value = ListBox1Value & Delimiter & .List(i)
                End If
            Next i
            ListBox1Value = Mid(ListBox1Value, Len(Delimiter) + 1)
        End If
    End With
End Property

Property Let ListBox1Value(inVal As String)
    Const Delimiter As String = ","
    Dim i As Long, j As Long
    Dim Words As Variant, inSelected() As Boolean
    Words = Split(LCase(inVal), Delimiter)
    
    With ListBox1
        ReDim inSelected(0 To .ListCount - 1)
        For i = 0 To UBound(Words)
            For j = 0 To .ListCount - 1
                If LCase(.List(j)) = Words(i) Then
                    inSelected(j) = True
                    Exit For
                End If
            Next j
            If j = .ListCount Then Err.Raise vbObjectError + 2147221884
        Next i
        For j = 0 To .ListCount - 1: .Selected(j) = inSelected(j): Next j
    End With
End Property

Note that ListBox1Value is a property of the user form not of the List Box.
ListBox1Value will return a comma delimited string of the items selected in ListBox1.
You and also use this property to select items.

If ListBox1 contains:

Apple
Banana
Coconut
Doughnut
Eggplant

UserForm1.ListBox1Value = "Banana,Doughnut" will select items 1 and 3 of the list (0 based)

You could use it to keep track of the selected items in a Label with code like

Private Sub ListBox1_Change()
    Label1.Caption = Me.ListBox1Value
End Sub

 

[AlexanderBB]

Thanks Mike, that was very useful.
I found MultiSelectExtended (2) works best but doesn't allow me to clear all selected with ListIndex= -1
So I've resorted to this

Sub ClearBox(n)
Dim a As Integer, i As Integer

Select Case n
   Case "ListBox1"
    a = 0
   Case "ListBox2"
    a = 1
   Case "ListBox3"
   a = 6
End Select

With Controls(a)
    For i = 0 To .ListCount - 1
        If .Selected(i) Then .Selected(i) = False
    Next i
End With

End Sub

n is ListBoxX.Name
But I should be able to pass in the name (or the listbox itself) and not need to define Controls (a).
Haven't managed that yet.

 

[mikerickson]

You can just declare the Listbox as an argument of the sub

Sub UnSelectAll(aListBox as MsForms.ListBox)
    Dim i as Long
    With aListBox
        For i = 0 to .ListCount - 1
            .Selected(i) = False
        Next i
    End With
End Sub

Private Sub CommandButton1_Click
    If ListBox1.MultiSelect = fmMultiSelectSingle Then
        ListBox1.ListIndex = -1
    Else
        Call UnSelectAll(ListBox1)
    End If
End Sub

Note that the branching in the Click event is only for comparison purposes, setting all the Selection(i) to False works with a single select ListBox.
Note also that the conditional is removed from the UnSelect loop. Setting everything to False saves the time taken by evaluating and branching the IF.

 

[AlexanderBB]

Hi Mike, that's almost what I first tried - and din't worjk ! Run-time error '424': Object required.

To correct this (and I don't understand it) is your Call UnSelectAll(ListBox1) rather than UnSelectAll(ListBox1)
I always thought they were the same thing, except for bracketing.

 

[mikerickson]

You could use either

Call UnSelectAll(ListBox1)

' or

UnSelectAll ListBox1

I'm not sure why it doesn't like the brackets in the second formulation. Both formulations do the same thing.

 

[My Aswer Is This]

Try this:
ListBox1.Clear

 

[AlexanderBB]

Intent isn't to clear all the items , just unselect the selected ones.
And Mike I came to that conclusion too, but after trying all the wrong things!