PDA

View Full Version : Solved: Entering only values that are different to a list box



RECrerar
09-04-2007, 03:31 AM
Hi, this is mainly a question of interest. I am populating a list box from a a column of data in a spreadsheet. There are several occurances of the same value in the column but I only want each different value to appear once in the listbox. For example the data may be:

test1
test1
test1
test2
test3
test3
etc....

and I would want the listbox to contain:

test1
test2
test3

The data is sorted and the code I am using (below) works fine

Private Sub UserForm_Initialize()

Dim row As Integer
row = 2

Do Until Cells(row, 1).Value = ""
If Cells(row, 1).Value <> Cells(row - 1, 1).Value Then
ListBox2.AddItem Cells(row, 1).Value
End If
row = row + 1
Loop

End Sub

The question is, is there a way to compare the values in the spreadsheet with the values already in the listbox rather than with the value in the spreadsheet so that if the data wasnt sorted I would still only get one occurence of each different value?

rory
09-04-2007, 03:39 AM
I would use a Collection (or Dictionary) object to hold the values as you add them to the list box. An error will be triggered if you try and add a key to a collection that already exists, so you can use a combination of On Error Resume Next and a test for Err.Number = 0 to determine whether to add the item to the Collection and the listbox.

RECrerar
09-04-2007, 04:53 AM
Thanks, that sounds exactly the sort of thing I'm after. i'm afraid I'll need some help working out how to do this though. Looking in the VBA help it gives the following as example code


The following code illustrates how to create a Dictionary object:
Dim d 'Create a variable
Set d = CreateObject(Scripting.Dictionary)
d.Add "a", "Athens" 'Add some keys and items
d.Add "b", "Belgrade"
d.Add "c", "Cairo"...

Do I need to define keys? That doesn't seem necessary for my application.

so the question is basically how do I create a collection (or dictionary) object?

rory
09-04-2007, 05:07 AM
Something like this:

Private Sub UserForm_Initialize()

Dim row As Long ' don't use integer for row counters as they stop at 32767
Dim dicItems As Object
Set dicItems = CreateObject("Scripting.Dictionary")
row = 2

Do Until Cells(row, 1).Value = ""
If Not dicItems.Exists(Cells(row, 1).Value) Then
ListBox2.AddItem Cells(row, 1).Value
dicItems.Add Cells(row, 1).Value, Cells(row, 1)
End If
row = row + 1
Loop

End Sub

RECrerar
09-04-2007, 05:34 AM
Thanks, that works perfectly greatly appreciated