I am using below code to let the user select items from the list while filling up the userform.

However when user select other, I want to deselect all items from the same listbox if he has selected any other than the "Other" item.

Below is the code, I am trying, however I don't know how I can deselect all items in userform if the User selects "Other" item in the listbox.

    For x = 0 To Me.LBX2.ListCount - 1
        If Me.LBX2.Selected(x) Then
            If Me.LBX2.List(x) = "Other" Then
                Me.LBX2.Selected(x) = False      '---This doesn't Deselect the items, not sure why-----
                If myVar1 = "" Then
                    myVar1 = Me.LBX2.List(x, 0)
                Else
                    myVar1 = myVar1 & vbLf & Me.LBX2.List(x, 0)
                End If
            End If
        End If
    Next x

CodePudding user response：

Unselect Multiselect Listbox items conditionally

If I understand correctly you have two scenarios:

• an element Other has been selected: ~~> unselect all previously selected items other than the selected list element "Other" (memorizing the remaining items though)
• no element Other has been selected: ~~> memorize all selected items (which of course do not comprise the string "Other")

You might code as follows collecting all selected item(numbers) - with the exception of "Other" - in array no:

Private Sub CommandButton1_Click()
    Dim elem: ReDim elem(0 To Me.LBX2.ListCount - 1)
    Dim i As Long, nxt As Long: nxt = -1
'a) check element (numbers) of selected items
    For i = 0 To Me.LBX2.ListCount - 1
        If Me.LBX2.Selected(i) Then
            If Me.LBX2.List(i, 0) = "Other" Then
                Dim unselect As Boolean
                unselect = True     ' get status "unselect"
            Else                    ' selected element other than "Other"
                nxt = nxt   1       ' increment counter
                elem(nxt) = i       ' remember element number
            End If
        End If
    Next
    If nxt > -1 Then
        ReDim Preserve elem(0 To nxt)            ' reduce array length to found items
    Else: Debug.Print "Nothing found to (un)select!": Exit Sub
    End If
'b) remember number & value of selected elements other than "Other"
    If unselect Then
        Debug.Print "A) Unselected: " & vbNewLine & " #";
        For i = 0 To UBound(elem)
            Me.LBX2.Selected(elem(i)) = False   ' << unselect
            elem(i) = elem(i) & " " & Me.LBX2.List(elem(i), 0)
        Next
    Else
        Debug.Print "B) Selected: " & vbNewLine & " #";
        For i = 0 To UBound(elem)
            elem(i) = elem(i) & " " & Me.LBX2.List(elem(i), 0)
        Next
    End If
'c) display un|selected element (number plus value) in VB Editor's immediate window
    Debug.Print Join(elem, vbNewLine & " #")
End Sub

Just to make the question reproducible with some pseudo data, I add the following initializing procedure:

Private Sub UserForm_Initialize()
    With Me.LBX2
        .MultiSelect = fmMultiSelectMulti
        .List = Split("a,b,Other,c,d,e", ",")
    End With
End Sub

CodePudding user response：

With multiple selects the Click event is not available and using the Change event requires some logic to avoid an endless loop.

Option Explicit

Public EnableEvents As Boolean

Private Sub ListBox1_change()
    If Not Me.EnableEvents Then Exit Sub
    
    Dim isOther As Boolean, i As Long, n As Long
    Dim var1 As String
    
    With ListBox1
        n = .ListIndex
        If n < 0 Then Exit Sub
        If .List(n) = "Other" Then
            isOther = True
            var1 = .List(n)
        End If

        Me.EnableEvents = False
        For i = 0 To .ListCount - 1
            If isOther Then
               If i <> n Then .Selected(i) = False
            ElseIf .List(i) = "Other" Then
               .Selected(i) = False
            ElseIf .Selected(i) Then
                If Len(var1) Then var1 = var1 & vbLf
                var1 = var1 & .List(i)
            End If
        Next
        Me.EnableEvents = True
    End With
    MsgBox var1

End Sub

Private Sub UserForm_Initialize()
    EnableEvents = True
End Sub