Prevent duplicates from adding items from Listbox1 to Listbox2

Question

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:

'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

Sam · Accepted Answer · 2013-11-03 21:12:04Z

0

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.

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

edited Nov 3, 2013 at 21:12

answered Nov 3, 2013 at 20:10

Sam

7,3333 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yoko21 Over a year ago

Thanks Sam. Your code is working for me. However, I need multiselect. Well, for now, as a temporary solution. I just set the "add" button to remove the item from listbox1 whenever selected to be added to listbox2. That way it will never be selected again. Thanks for the help.

Sam Over a year ago

It should be resonably easy to adapt to work with multiselect boxes, you would just have to loop through the selected values and use the same logic. I don't understand though, if you're removing the item from Listbox1 then why do you need the duplicate check?

Sam Over a year ago

@Yoko21 I updated the code. It should now work if ListBox1 is multi-select. Also the item will be removed from ListBox1 when it's added to listbox2

Ben Smith · Accepted Answer · 2015-08-12 12:46:37Z

0

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.

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.

answered Aug 12, 2015 at 12:46

Ben Smith

92 bronze badges

Collectives™ on Stack Overflow

Prevent duplicates from adding items from Listbox1 to Listbox2

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related