Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Duplicates in ListBox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Duplicates in ListBox
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Duplicates in ListBox
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox 2 takes the value of Listbox 1 | Excel Discussion (Misc queries) | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Listbox B if LIstbox A equals | Excel Discussion (Misc queries) | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |