How do I show a list of unique items in a ComboBox? 1

[mtroche]

I have a ComboBox showing the items from an array, but there are some repeated items and I don't want to show the same item twice or more in the ComboBox. How can I do it? How can I make the ComboBox to show each item from the same array just one time and don't show repeated items?

I will appreciate any help.

 

[jproj]

mtroche:

When adding items to your combo box, set up a loop to go through your array. Before adding the next variable to your combo box, you will want to check if it is already there... if it is, don't add it. I started with 20 numbers in cells A1 to A20 then added all the numbers (excluding the duplicates) to ComboBox1 on a UserForm. See my code below:

Private Sub UserForm_Initialize()
Dim Variable As String, i As Integer, cboItem As _
Integer, counter As Integer
For i = 1 To 20
Variable = Cells(i, 1)
counter = 0
If ComboBox1.ListCount = 0 Then
ComboBox1.AddItem (Variable)
Else
ComboBox1.ListIndex = -1
For cboItem = 0 To ComboBox1.ListCount
Select Case cboItem
Case Is <> ComboBox1.ListCount
If ComboBox1.Value = Variable Then
counter = 1
End If
ComboBox1.ListIndex = ComboBox1.ListIndex + 1
Case Is = ComboBox1.ListCount
If counter <> 1 Then
ComboBox1.AddItem (Variable)
End If
End Select
Next cboItem
End If
Next i
ComboBox1.ListIndex = 0
End Sub

Hope this Helps!

Jproj

 

[msmith]

mtroche & jproj,

For an alternative method, check out this tip on John Walkenbach's Spreadsheet Page site:

http://www.j-walk.com/ss/excel/tips/tip47.htm

His code grabs data from a worksheet, but you should be able to modify it to act on an array.


Regards,
M. Smith