Getting Duplicates in ListBox

[Tony Bender]

My application has a UserForm with some ListBoxes that when activated
load in a list of accounts. The user selects an account and the
appropriate information appears in the template, and the UserForm
'hides'. The user can then change the account selection by clicking a
CommandButton that re-activates the UserForm with the account
selection ListBox. The problem I have is each time the user goes
through this sequence the account list duplicates itself, so that
after say 5 cycles, there are 5 series of the list in the ListBox.
Not a big deal since it does not adversely affect my application, it
is more an annoyance since I thought my corresponding code would
prevent this duplication. Here is my code and I greatly appreciate
learning where I may have gone wrong.

Private Sub UserForm_Activate()
'******THIS AUTOMATICALLY LOADS THE LIST OF ACCOUNTS INTO THE LISTBOX
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items are in b2:b102
Application.ScreenUpdating = False

Sheets("list").Select
Set AllCells = Range("c2:c214")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next

For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a
string
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
UserForm3.ListBox1.AddItem Item
Next Item

'set focus to listbox1
'Sheets("report").Select'

Call LoadBrands
'UserForm3.CommandButton1.SetFocus
UserForm3.ListBox1.SetFocus
End Sub


TIA

Tony

 

[JellyBean]

Try adding "cmb_ClientName.Clear", where "cmb_ClientName" is the name of my
object (a ComboBox/List box) and the ".clear" clears the list.

You'll need this "command" in the appropriate place.

 

[Konrad]

insert this line
UserForm3.ListBox1.Clear
before
For Each Item In NoDupes
UserForm3.ListBox1.AddItem Item
Next Item

Konrad
email welcome if its working remove _nospam