avoiding duplicates in listbox (added from another listbox)

[KR]

I have a userform with a list of source names, a second list box in which to
put selected names, and an add button to move the selected names to the
second listbox.

I need to avoid having the same name added more than once, and although the
code below seems like a good idea to prevent duplicates, I'm getting a type
mismatch. I think I'm mis-referencing the second element in the match
statement, but I'm not sure how to reference the list inside the second
listbox.

Please help? I'm using XL2003

Thanks,
keith

For i = 0 To frmEntry.lstSource.ListCount - 1
If frmEntry.lstSource.Selected(i) = True Then
If IsError(Application.Match(frmEntry.lstSource.List(i),
frmEntry.lstSelected.List, 0)) Then '*** mismatch error
frmEntry.lstSelected.AddItem frmEntry.lstSource.List(i)
End If
End If
Next i

 

[Dave Peterson]

How about deleting them from the first listbox when you add them to the second.
It might be easier to code and more intuitive for the user.

This may give you an idea:
http://groups.google.com/[email protected]

 

[KR]

There are three source lists that can populate the source listbox (changed
by clicking on one of three cmdbuttons), as the user needs to add names from
more than one of the lists. I've kept the lists separate because the
grouping is important (for example, they may need to pick one name from
/each/ list for a total of three unique names, but some names may be on 2 or
even all 3 lists).

So they may look at source list 1, add one (or more) names, then click to
see the second source list, add one (or more) names, etc. Since they can't
use the same person more than once, it is more reliable to programatically
prevent repeats rather than rely on the users to avoid duplicates.

Should I just add the names to an array in memory instead of trying to match
against the destination listbox? I can do that, I just thought there might
be a more elegant way to compare directly to the array that is already in
the listbox.

FWIW, the example referenced in the URL is even more elegant than what I'm
doing, as I'm not removing items from the source list (e.g. select with
replacement) because otherwise I'd have to keep track of each list even when
it wasn't showing (user switches lists, then switches back) and also which
list(s) the duplicates names are on - I'd have to check all three source
lists to remove a name that was selected, then know which list(s) to put the
names back on if it was unselected.

Thanks,
Keith

 

[Dave Peterson]

I built a userform with 3 Input Listboxes (listbox1, listbox2, listbox3)
and one "output" listbox (listbox4)

And 4 commandbuttons (cancel, Process Listbox1, process listbox2, and process
listbox3):

This was the code I used to test it:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Call DoTheWork(Me.ListBox1)
End Sub
Private Sub CommandButton3_Click()
Call DoTheWork(Me.ListBox2)
End Sub
Private Sub CommandButton4_Click()
Call DoTheWork(Me.ListBox3)
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
Dim lCtr As Long

'some test data
For lCtr = 1 To 3
With Me.Controls("Listbox" & lCtr)
.MultiSelect = fmMultiSelectMulti
For iCtr = 1 To 5
.AddItem "asdf" & Format(iCtr, "00")
Next iCtr
End With
Next lCtr

For iCtr = 1 To 3
Me.Controls("Commandbutton" & iCtr + 1).Caption _
= "Process ListBox " & iCtr
Next iCtr
End Sub
Sub DoTheWork(myListBox As Control)

Dim iCtr As Long
Dim jCtr As Long
Dim res As Variant
Dim myArr As Variant

If myListBox.ListCount > 0 Then
For iCtr = 0 To myListBox.ListCount - 1
If myListBox.Selected(iCtr) Then
If Me.ListBox4.ListCount = 0 Then
Me.ListBox4.AddItem myListBox.List(iCtr)
Else
ReDim myArr(0 To Me.ListBox4.ListCount - 1)
For jCtr = 0 To Me.ListBox4.ListCount - 1
myArr(jCtr) = Me.ListBox4.List(jCtr, 0)
Next jCtr
res = Application.Match(myListBox.List(iCtr), myArr, 0)
If IsError(res) Then
'add it
Me.ListBox4.AddItem myListBox.List(iCtr)
Else
Beep
End If
End If
End If
Next iCtr
End If
End Sub

 

[KR]

Thanks Dave! I've adapted my code to add the items to an array (per your
sample code) and the comparison works great- no duplicates!

Many thanks,
Keith