×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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.

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

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources