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)



"PaulW" <(E-Mail Removed)> wrote in message
news:70A90A00-0DC9-4968-8A96-(E-Mail Removed)...
> 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

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

"PaulW" wrote:

> 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

Excellent thanks guys, once again you help me out of a tight spot.

"Joel" wrote:

> 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
>
> "PaulW" wrote:
>
> > 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