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

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

Filtered list for Combo Box ListFillRange - Nested Combo Boxes
 

Thanks Tom, much obliged!!

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