防止重复项从Listbox1添加到Listbox2（VBA excel）

I have two ListBoxes. ListBox1 has list of items that can be selected by the user to transfer to ListBox2 by either double clicking the item or pressing the add button. What I want to do now is to prevent the user from adding duplicates in ListBox2. If ever a duplicate is detected a message will prompt "Item already included" and end the code. I am guessing this can be done with contains? But I have no idea how to do it. I have the following codes:

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

'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

2 个解决方案

#1

Something like this will hopefully help you..

这样的东西有望帮助你..

AddValueListbox2 function checks for existence of a value, adds it if it's not there and alerts the user if it is.

AddValueListbox2函数检查是否存在值，如果不存在则添加它，如果存在则向用户发出警报。

NB This will work if you have multi-select enabled for the list boxes.

注意如果您为列表框启用了多选，则此功能将起作用。

 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

#2

In case anyone is still interested, there's another way to do this, using a similar technique.

如果有人仍然感兴趣，还有另一种方法可以使用类似的技术。

Sub Duplicate()

 dim i as integer
 dim x as integer
 x = 0

  For i = 0 to listbox2.count - 1 
     If listbox2.list(i) = myval Then
        x = x + 1
     End If
  Next i

  If x = 0 Then
     listbox2.additem myval
  End If

End Sub

Where myval is the selected value from listbox1.

其中myval是listbox1中的选定值。

Essentially if it finds a single reference to your value in the list, it will start a counter. If no instances of your value are found, it will insert it into the listbox.

基本上，如果它在列表中找到对您的值的单个引用，它将启动一个计数器。如果未找到您的值的实例，则会将其插入列表框中。

Hope this helps someone.

希望这有助于某人。

#1