Move items from one listbox to another without duplicating

Question

I have a userform with two listboxes side by side. I want the left listbox to contain a large list of items, and the user can select those they want and send them to the listbox on the right. This does not remove the items from the listbox on the left. Items on the left are unique.

I don't want users to be able to send the same item twice to the list on the right, so I have the following sub to check for duplicates first:

Sub ToRight(ctrlLeft As control, ctrlRight As control)
    Dim i As Integer, j As Integer
    Dim there As Boolean

    For i = 0 To ctrlLeft.ListCount - 1
        If ctrlLeft.Selected(i) = True Then
                there = False
                For j = 0 To ctrlRight.ListCount - 1
                    If ctrlRight.List(j) = ctrlLeft.List(i) Then
                        there = True
                    End If
                Next
                If there = False Then ctrlRight.addItem ctrlLeft.List(i)
        End If
    Next
End Sub

For each selected item in the left listbox, it will check every item in the right listbox to see if there is a match, and only add it if there is no match. That is very slow once there are around 1000 entries in the list (it can happen) and the userform actually hides itself after running the code (5 seconds). I have to minimize and re-maximize the Excel application for the userform to show again (and it is modal).

How can I send items to the right listbox without such a painful loop? Or how can I make the loop less expensive so it doesn't crash the userform?

When you move it to the right listbox, remove it from the left and vice versa so that they cannot add the same item twice. Simple as that :) — Siddharth Rout
@SiddharthRout I have thought of doing that, but the items are ordered according to our nomenclature, the order has a sense and users would expect to see the listed items in that order. If I remove it from the left list, and a user decides they don't want to have that item after all, sending it back to the left will screw up the order by putting it on the end — David G
If you can explain the order then maybe we can put it back at the same position? — Siddharth Rout
Or you can prefix the items by 1., 2. 3. That ways you will know which position it belongs to? — Siddharth Rout
See my previous comment. Either you can add a prefix or store the list in a hidden sheet. The list will be in col B and the positions will be in col A. So when the user moves it back, simply check the position in the hidden sheet ;) — Siddharth Rout

Unknown Unknown · Accepted Answer · 2016-08-11T16:22:23

A Scripting Dictionary is ideal for comparing multiple list.

Private Sub btnCopyUniqueSelectedItems_Click()

    Dim i As Integer
    Dim dictItems As Object
    Set dictItems = CreateObject("Scripting.Dictionary")

    For i = 0 To ctrlRight.ListCount - 1

        dictItems.Add ctrlRight.List(i), vbNullString

    Next

    For i = 0 To ctrlLeft.ListCount - 1
        If ctrlLeft.Selected(i) = True And Not dictItems.Exists(ctrlLeft.List(i)) Then

            ctrlRight.AddItem ctrlLeft.List(i)

        End If
    Next

End Sub

Move items from one listbox to another without duplicating

2 Answers

A Scripting Dictionary is ideal for comparing multiple list.