Populate a Combobox from range.

[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

[jindon]

try

Please Login or Register  to view this content.

[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

[jindon]

Yup!

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

dic.comparemode=vbtextcompare

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> ex1302's Profile:
> http://www.excelforum.com/member.php...o&userid=25217
> View this thread: http://www.excelforum.com/showthread...hreadid=389308
>

[ex1302]

Thanks, it works great.....

can this be extended to work across two columns?

ie A1:B2

Regards,

Andy

[jindon]

Why don't you try?

It should work with that range

[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 a combobox, splitting the two columns in the range out into two columns in the combobox?

Regards

Andy

[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 dropdown box that relies upon a spreadsheet column, this column could contain multiples so therefore will need to be de-duped.

Secondly in another column, could be adjacent could be not! (but is 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, and therefore duplicated as well.

So the rquirement is to grab a range, de-dup throw into a combo box and then somehow also grab the second range(which i can do), but also include this in the combobox OR after the user has chosen the de-duped 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 do , but am stuck on the send bit of pulling in the related data to the selected value.

for example;

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

Regards,

Andy

[jindon]

eliminate dups on col.A

Please Login or Register  to view this content.

no elimination of dups

Please Login or Register  to view this content.

[ex1302]

Cool,

Will give it a go.


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

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