Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox.additem (No Repeats)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox.additem (No Repeats)
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox.additem (No Repeats)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox.additem (No Repeats)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unique text additem combobox | Excel Programming | |||
additem to combobox with an array | Excel Discussion (Misc queries) | |||
combobox additem | Excel Programming | |||
AddItem Method - Combobox value | Excel Programming | |||
Multiple Column ComboBox using Additem | Excel Programming |