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

--
ex130
-----------------------------------------------------------------------
ex1302's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2521
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

[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

Post by ex1302
Hi all,
Firstly, how can I populate a combobox on a userform from range on
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,
Andy
--
ex1302
------------------------------------------------------------------------
http://www.excelforum.com/member.php?action=getinfo&userid=25217
View this thread: http://www.excelforum.com/showthread.php?threadid=389308

[jindon]

Why don't you try?

It should work with that rang

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

[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

--
ex130
-----------------------------------------------------------------------
ex1302's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2521
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

[jindon]

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

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

[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

--
ex130
-----------------------------------------------------------------------
ex1302's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2521
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

[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]

The second one works,

Thanks!


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

--
ex130
-----------------------------------------------------------------------
ex1302's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2521
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

[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?

[ex1302]

Cool,

Will give it a go. :)


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

--
ex130
-----------------------------------------------------------------------
ex1302's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2521
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

[ex1302]

Thanks, it works great.....

can this be extended to work across two columns?

ie A1:B2

Regards,

And

--
ex130
-----------------------------------------------------------------------
ex1302's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2521
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

[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

--
ex130
-----------------------------------------------------------------------
ex1302's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2521
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

[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

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=38930

[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

-------------------

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=38930