excel - 防止重复项从列表框添加到列表框(VBA excel)

Question

我有两个列表框。 ListBox1 具有项目列表，用户可以通过双击项目或按下添加按钮来选择这些项目以传输到 ListBox2。我现在要做的是防止用户在 ListBox2 中添加重复项。如果检测到重复项，则会提示“项目已包含”并结束代码。我猜这可以用包含来完成？但我不知道该怎么做。我有以下代码:

'Report Listing
Private Sub UserForm_Initialize()
    'List of Reports
    With ListBox1
        .AddItem "Report 1"
        .AddItem "Report 2"
        .AddItem "Report 3"
        .AddItem "Report 4"
        .AddItem "Report 5"
        .AddItem "Report 6"
    End With
End Sub

'Add selection to ListBox2
Private Sub AddButton_Click()

        With ListBox1
        Dim itemIndex As Integer
        For itemIndex = .ListCount - 1 To 0 Step -1
            If .Selected(itemIndex) Then
                ListBox2.AddItem .List(itemIndex)
            End If
        Next itemIndex
    End With

End Sub

    'Double click to Add
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ListBox2.AddItem ListBox1.List(ListBox1.ListIndex)
End Sub

Answer 1

像这样的东西希望能帮助你..
AddValueListbox2函数检查值是否存在，如果不存在则添加它，如果存在则提醒用户。

注意
这个将如果您为列表框启用了多选，则可以使用。

 Private Sub CommandButton1_Click()

'index is -1 if nothin is selected
If ListBox1.ListIndex = -1 Then Exit Sub

'loop backwards as we're removing items
For i = ListBox1.ListCount - 1 To 0 Step -1

    If ListBox1.Selected(i) Then

        AddValueListbox2 ListBox1.List(i)
        ListBox1.RemoveItem (i)

    End If
Next i

End Sub


Private Function AddValueListbox2(str As String)

Dim valExists As Boolean

valExists = False

    For i = 0 To ListBox2.ListCount - 1

        If ListBox2.List(i) = str Then valExists = True

    Next i

    If valExists Then

        MsgBox str & " has already been added to ListBox", vbInformation

    Else

        ListBox2.AddItem str

    End If

End Function

Private Sub UserForm_Activate()

Dim items(2) As String

items(0) = "foo"
items(1) = "bar"
items(2) = "baz"


    For i = LBound(items) To UBound(items)
        Me.ListBox1.AddItem items(i)
    Next i

End Sub