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, befo=j
NoDupes.Add Swap2, befo=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

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

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.



"Tony Bender" wrote in message
om...
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, befo=j
NoDupes.Add Swap2, befo=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