Populate a Combobox from range.

  • Thread starter Thread starter ex1302
  • Start date Start date
E

ex1302

Hi all,

Firstly, how can I populate a combobox on a userform from range o
sheet1 using vba, lets say "A2:A4".

Secondly, if this range contains duplicate values how can i "groupby
to produce a non dupliacted list of values for the combobox.

Regards,

And
 
try

Code
-------------------

Private Sub UserForm_Initialize()
Dim dic As Object, x, r As Range
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("sheet1")
For Each r In .Range("a2:a4")
If Not IsEmpty(r) And Not dic.exists(r.Value) Then
dic.Add r.Value, Nothing
End If
Next
End With
x = dic.keys
Me.ComboBox1.List() = x
Set dic = Nothing: Erase x
End Sub
 
Hi,

You can normally populate a combobox from range using the following
instruction:

ComboBox1.List=Range("A2:A10")

However, if you want to remove dups, you could try this code:

Private Sub UserForm_Initialize()
Dim c As Range, D As Object
Set D = CreateObject("Scripting.Dictionary")
For Each c In Sheets("Sheet1").Range("A2:A10")
If Not D.Exists(c.Value) Then D.Add c.Value, 1
Next c
ComboBox1.List = Application.Transpose(D.Keys)
End Sub

Regards,
KL
 
Yup!

Dictionary object can hold only unique values and it is cas
sensitive.
If you don't want to case sensitive, add following line after
set dic=Createobject(".......

dic.comparemode=vbtextcompar
 
Thanks, it works great.....

can this be extended to work across two columns?

ie A1:B2

Regards,

And
 
The code works great for putting it into one list column,

What i meant was, how can you add the data from the range A1:B3 into
combobox, splitting the two columns in the range out into two column
in the combobox?

Regards

And
 
good point..... :)

hmmm


Ok, the problem goes that the requirements have asked for a dropdow
box that relies upon a spreadsheet column, this column could contai
multiples so therefore will need to be de-duped.

Secondly in another column, could be adjacent could be not! (but i
findable), is a set of values related to the value in the first column
as these two are related the values will always be the same, an
therefore duplicated as well.

So the rquirement is to grab a range, de-dup throw into a combo box an
then somehow also grab the second range(which i can do), but als
include this in the combobox OR after the user has chosen the de-dupe
value in the combobox find the related value from the otherlist......

hope that all makes sense,

so my solution was to initally findout if i could grab the range
de-dup and then throw into the combobox, which thanks to you i can d
:) , but am stuck on the send bit of pulling in the related data to th
selected value.

for example;

cell:
A1 = 1
C1 = "Hello"
A2 = 2
C2 = "Goodbye"
A3 = 1
C3 = "Hello"

Regards,

And
 
eliminate dups on col.A

Code:
--------------------

Private Sub UserForm_Initialize()
Dim dic As Object, x, y, r As Range
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("sheet1")
For Each r In .Range("a2:a4")
If Not IsEmpty(r) And Not dic.exists(r.Value) Then
dic.Add r.Value, r.Offset(, 1).Value
End If
Next
End With
x = dic.keys: y = dic.items
ReDim a(UBound(x), 1)
For i = LBound(x) To UBound(x)
a(i, 0) = x(i): a(i, 1) = y(i)
Next
With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "20;20" 'alter to suite
.List() = a
End With
Set dic = Nothing: Erase x, a, y
End Sub
--------------------

no elimination of dups

Code:
--------------------

Private Sub UserForm_Initialize()

With Me.ComboBox1
.ColumnCount = 2
.ColumnWidths = "20;20" 'alter to suite
.List() = Sheets("sheet1").Range("a2:b4").Value
End With

End Sub
 
so how could i get this to work across two non contiguous rows?

ie A1:A3 and C1:C4

thanks,

Andy
 
Back
Top