Combobox.additem (No Repeats)

  • Thread starter Thread starter PaulW
  • Start date Start date
P

PaulW

Hi there,

I'm building a Userform with a bunch of Comboboxes which I am populating
from a spreadsheet.

One of the Combobox is beingin populated by a field which has the majority
of the entries the same.

Can anyone help with some code to only populate the Combobox with uniqie
entries only.

Code I have been using to populate the Combobox has been:

sub unserform_initialize ()
a = 2
do until cells(a,1)=""
combobox1.additem cells(a,24)
a = a+1
loop
end sub

Thanks
Dan
 
Private Sub Userform_Initialize()
Dim ary
Dim idx As Long
Dim rownum As Long

ReDim ary(1 To 1)
ary(1) = Cells(1, "A").Value

idx = 1
rownum = 2
Do Until Cells(rownum, "A") = ""
If UBound(Filter(ary, Cells(rownum, "A"))) = -1 Then
idx = idx + 1
ReDim Preserve ary(1 To idx)
ary(idx) = Cells(rownum, "A")
End If
rownum = rownum + 1
Loop
ComboBox1.List = ary
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
the code below check if item is in combobox before adding new item

Sub unserform_initialize()
a = 2
ActiveSheet.ComboBox1.Clear
Do Until Cells(a, 24) = ""
Found = False
For i = 0 To (ActiveSheet.ComboBox1.ListCount - 1)
If ActiveSheet.ComboBox1.List(i) = _
Format(Cells(a, 24), Text) Then
Found = True
Exit For
End If
Next i
If Found = False Then
ActiveSheet.ComboBox1.AddItem Cells(a, 24)
End If
a = a + 1
Loop
End Sub
 
Back
Top