VBA在特定條件下取消選擇用戶表單串列框中的專案

我使用下面的代碼讓用戶在填寫用戶表單時從串列中選擇專案。

但是，當用戶選擇其他時，如果他選擇了“其他”專案以外的任何專案，我想取消選擇同一個串列框中的所有專案。

下面是代碼，我正在嘗試，但是如果用戶在串列框中選擇“其他”專案，我不知道如何取消選擇用戶表單中的所有專案。

    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

uj5u.com熱心網友回復：

對于多選，Click 事件不可用，使用 Change 事件需要一些邏輯來避免無限回圈。

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

uj5u.com熱心網友回復：

有條件地取消選擇多選串列框專案

如果我理解正確，您有兩種情況：

• Other已選擇一個元素：~~> 取消選擇除所選串列元素“其他”以外的所有先前選擇的專案（不過記住其余專案）
• 沒有Other選擇元素：~~>記住所有選擇的專案（當然不包括字串"Other"）

您可以撰寫如下代碼收集所有選定的專案（數字） -除了"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

為了使問題可以用一些偽資料重現，我添加了以下初始化程序：

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

標籤：擅长 vba 用户表单

上一篇：將swModel.CreateText用于帶復選框的多行注釋

下一篇：VBA在數字上轉換時間