Filtered list for Combo Box ListFillRange - Nested Combo Boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Tom,
i'm not sure, but the code looks like it would work more my form, but i
don't reall know what i am doing. could you tell me what i am doing wrong.

I have two comboboxes (cmb1 & cmb2)
cmb1 is doing what i want it to, but i can't get cmb2 to work with the code
you gave here. am i supposed to do something with ListFillRange?
here is how i modified it.

Private Sub cmbJobs_Change()
Dim cell As Range
cmb2.Clear

For Each cell In Range("inv_alldata").Columns(6).Cells
If cell.Value = cmb1.Value Then
cell.Select
ActiveCell.Offset(0, -5).Select
cmb2.AddItem ActiveCell.Value
End If
Next
End Sub



Tom Ogilvy said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top