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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

Thanks Tom, much obliged!!

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.ListCount - 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>