防止重复项从 Listbox1 添加到 Listbox2 (VBA excel)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19755920/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Prevent duplicates from adding items from Listbox1 to Listbox2 (VBA excel)
提问by Yoko21
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:
我有两个列表框。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
回答by Sam
Something like this will hopefully help you..
像这样的东西希望能帮助你..
AddValueListbox2function checks for existence of a value, adds it if it's not there and alerts the user if it is.
AddValueListbox2函数检查值是否存在，如果不存在则添加它，如果存在则提醒用户。
NBThis willwork 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
回答by Ben Smith
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.
希望这可以帮助某人。
