Populate a Combobox from range.

[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

 

[jindon]

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

 

[KL]

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

 

[ex1302]

Thanks ill give it a go,

does it de-duplicate?

Andy

ps for those not aware, breaking news concerning london...
'www.bbc.co.uk/news' (http://www.bbc.co.uk/news

 

[jindon]

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

 

[ex1302]

Thanks, it works great.....

can this be extended to work across two columns?

ie A1:B2

Regards,

And

 

[jindon]

Why don't you try?

It should work with that rang

 

[ex1302]

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

 

[jindon]

How do you want it if you have dups in one column

 

[ex1302]

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

 

[jindon]

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

 

[ex1302]

Cool,

Will give it a go.


ps for those not aware, breaking news concerning london...
www.bbc.co.uk/new

 

[ex1302]

The second one works,

Thanks!


-Keep safe in the "big smoke"....

 

[ex1302]

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

ie A1:A3 and C1:C4

thanks,

Andy

 

[jindon]

Andy,

How many rows do you expect?