Loading Combobox, but avoiding duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column of values and can load them into a combo box.
But I want to avoid loading duplicate values.
I can't figure out how to do this. This is the latest attempt:

For Each c In Sheet1.Range("Customer") ' "customer" is a named range
If c.Row > 6 Then ' I only want from column 6 and higher
If c.Value = Empty Then 'end of the road
Exit For
End If
For n = 0 To cbxCompany.ListCount
If c.Value = frmNewMeeting.cbxCompany.ListIndex(n).Value Then
'don't add this item, it's a duplicate
dup = True
End If
Next n

If dup = False Then
frmNewMeeting.cbxCompany.AddItem (c.Value)
End If
End If
Next c

I get a "Type Mismatch" error on this line:
If c.Value = frmNewMeeting.cbxCompany.ListIndex(n).Value Then
but I can't figure out why... or how else to do it.

I just want to compare c.value (the contents of the next cell) to all the
values in the
combobox to see if it's a duplicate before I add it.


Thanks in advance!

Dan
 
Hi Dan,
I just want to compare c.value (the contents of the next cell) to all the
values in the
combobox to see if it's a duplicate before I add it.
Click to expand...

I use a special function for that:

Function IsInCombo(oCombobox As MSForms.ComboBox, sItem As String, _
Optional iColumn As Integer=0) As Boolean
Dim lCount As Long
On Error Resume Next
For lCount = 0 To oCombobox.ListCount - 1
If oCombobox.List(lCount, iColumn) = sItem Then
IsInCombo = True
Exit Function
End If
Next
End Function

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com
 
ListIndex is meant only to show, which item is selected in a combobox and can't be used this way... use frmNewMeeting.cbxCompany.List(n)
 
Back
Top