Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Combobox, but avoiding duplicates
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Combobox, but avoiding duplicates
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. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Combobox, but avoiding duplicates
Jan - Thanks so much, that looks like it will solve my problem.
Dan "Jan Karel Pieterse" wrote: 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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoiding Duplicates | Excel Discussion (Misc queries) | |||
AVOIDING DUPLICATES IN A RANGE OF CELLS | Excel Discussion (Misc queries) | |||
Avoiding Duplicates | Excel Discussion (Misc queries) | |||
avoiding duplicates | Excel Programming | |||
Avoiding duplicates | Excel Programming |