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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 07:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com