Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
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...fo&userid=2521 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
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...fo&userid=1313 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
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...fo&userid=2521 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
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...fo&userid=1313 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
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...fo&userid=2521 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
Why don't you try? It should work with that rang -- jindo ----------------------------------------------------------------------- jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
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...fo&userid=2521 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
How do you want it if you have dups in one column -- jindo ----------------------------------------------------------------------- jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
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...fo&userid=2521 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
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 -------------------- -- jindon ------------------------------------------------------------------------ jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135 View this thread: http://www.excelforum.com/showthread...hreadid=389308 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
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...fo&userid=2521 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
The second one works, Thanks! -Keep safe in the "big smoke".... -- ex130 ----------------------------------------------------------------------- ex1302's Profile: http://www.excelforum.com/member.php...fo&userid=2521 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
so how could i get this to work across two non contiguous rows? ie A1:A3 and C1:C4 thanks, Andy -- ex1302 ------------------------------------------------------------------------ ex1302's Profile: http://www.excelforum.com/member.php...o&userid=25217 View this thread: http://www.excelforum.com/showthread...hreadid=389308 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a Combobox from range.
Andy, How many rows do you expect? -- jindon ------------------------------------------------------------------------ jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135 View this thread: http://www.excelforum.com/showthread...hreadid=389308 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i populate a text box according to selection in combobox? | Excel Worksheet Functions | |||
Populate combobox | Excel Programming | |||
populate combobox with sheet names | Excel Programming | |||
Populate a combobox | Excel Programming | |||
Populate Column Header on ComboBox | Excel Programming |