Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtered list for Combo Box ListFillRange - Nested Combo Boxes
ComboBox1 ListFillRange is Range1 and linked cell is Combo1Code
ComboBox2 ListFillRange is [Range2] where [Range2] must be a subset of the whole Range2, where FieldA is equal to Combo1Code Is there a way to filter this Range2 on the fly (in memory) and therefore code it in the ListFillRange property ComboBox2 or should I physically create the filtered new range as Range3 in the worksheet in the ComboBox1_Change event and use Range3 in ComboBox2's ListFillRange property? Tx in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtered list for Combo Box ListFillRange - Nested Combo Boxes
You can do the latter (range3) or you can not use the listfillrange
property and just use additem to fill the combobox2 list. Some pseudo code: Private Sub Combobox1_Click() Dim cell as Range Combobox2.Clear Combobox2.ListCount = 2 for each cell in Range("Range2").Columns(1).Cells if cell.Value = Combobox1.Value then Combobox2.AddItem cell.Value Combobox2.List(Combobox2.Listcount-1,1) = cell.offset(0,1) end if Next End Sub -- Regards, Tom Ogilvy "DoctorG" wrote in message ... ComboBox1 ListFillRange is Range1 and linked cell is Combo1Code ComboBox2 ListFillRange is [Range2] where [Range2] must be a subset of the whole Range2, where FieldA is equal to Combo1Code Is there a way to filter this Range2 on the fly (in memory) and therefore code it in the ListFillRange property ComboBox2 or should I physically create the filtered new range as Range3 in the worksheet in the ComboBox1_Change event and use Range3 in ComboBox2's ListFillRange property? Tx in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtered list for Combo Box ListFillRange - Nested Combo Boxes
Thanks Tom, much obliged!!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtered list for Combo Box ListFillRange - Nested Combo Boxes
Tom (or any helping soul out there)
I tried your code and managed to get it to work for a single column combo. I need a 2-column combo and I'm having trouble setting it up. Please take a look at the code below and tell me what I'm doing wrong. I only get 1 column of data. Mind you that I'm trying to set up the first row as titles / headings. If it's not too much please advise how I can replace the full reference to Sheet10.Ergo_Combo with a short local object, as you have done with Cell. D_Projects and Ergo_Combo are on different sheets and I need full reference. Thanks for your help .......................................... Dim Cell As Range Sheet10.Ergo_Combo.Clear Sheet10.Ergo_Combo.ColumnCount = 2 Sheet10.Ergo_Combo.AddItem Sheet10.Ergo_Combo.List(0, 1) = "Code" Sheet10.Ergo_Combo.List(0, 2) = "Description" For Each Cell In Range("D_Projects").Columns(2).Cells If Cell.Value = Range("C_Rep_Customer_Code").Value Then Sheet10.Ergo_Combo.AddItem Cell.Offset(0, 3).Value Sheet10.Ergo_Combo.List(Sheet10.Ergo_Combo.ListCou nt - 1, 2) = Cell.Offset(0, 1).Value End If Next If Sheet10.Ergo_Combo.ListCount = 1 Then MsgBox ("No entries") Sheet10.Ergo_Combo.Enabled = False Else Sheet10.Ergo_Combo.Enabled = True End If .................................................. ...... "Tom Ogilvy" wrote: You can do the latter (range3) or you can not use the listfillrange property and just use additem to fill the combobox2 list. Some pseudo code: Private Sub Combobox1_Click() Dim cell as Range Combobox2.Clear Combobox2.ListCount = 2 for each cell in Range("Range2").Columns(1).Cells if cell.Value = Combobox1.Value then Combobox2.AddItem cell.Value Combobox2.List(Combobox2.Listcount-1,1) = cell.offset(0,1) end if Next End Sub -- Regards, Tom Ogilvy "DoctorG" wrote in message ... ComboBox1 ListFillRange is Range1 and linked cell is Combo1Code ComboBox2 ListFillRange is [Range2] where [Range2] must be a subset of the whole Range2, where FieldA is equal to Combo1Code Is there a way to filter this Range2 on the fly (in memory) and therefore code it in the ListFillRange property ComboBox2 or should I physically create the filtered new range as Range3 in the worksheet in the ComboBox1_Change event and use Range3 in ComboBox2's ListFillRange property? Tx in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Combo boxes to change options based on other Combo boxes. | New Users to Excel | |||
Combo box - Listfillrange | Excel Discussion (Misc queries) | |||
Selecting subsets using combo boxes or list boxes | Excel Discussion (Misc queries) | |||
Questions on combo boxes and list boxes. | New Users to Excel | |||
Populate Combo Box With Filtered List | Excel Programming |