I have combobox1, combobox2 and listbox1.

i am runnig the code from sheet1

in my below code only for the first item column 2 and
three are displyed..

also how can I use dynamic range?

is it possible to get unique item from sheet2 Range(Range
("A2"),Range("A65536").end(xlup)) as combobox1 list?



Private Sub ComboBox1_Change()

Dim sh As Worksheet
Dim cell As Range

Me.ListBox1.Clear

Set sh = Sheets("sheet2")

For Each cell In sh.Range("a2:a10")
If cell.Value = Me.ComboBox1.Value And cell.Offset(0,
2).Value = ComboBox2.Value Then
'If cell.Offset(0, 2).Value = ComboBox2.Value Then
Me.ListBox1.AddItem cell.Value
Me.ListBox1.List(0, 1) = cell.Offset(0, 1).Value
Me.ListBox1.List(0, 2) = cell.Offset(0, 2).Value
'End If
End If
Next cell

End Sub

Private Sub ComboBox2_Change()

Dim sh As Worksheet
Dim cell As Range

Me.ListBox1.Clear

Set sh = Sheets("sheet2")

For Each cell In sh.Range("a2:a10")
If cell.Value = Me.ComboBox1.Value And cell.Offset(0,
2).Value = ComboBox2.Value Then
'If cell.Offset(0, 2).Value = ComboBox2.Value Then
Me.ListBox1.AddItem cell.Value
Me.ListBox1.List(0, 1) = cell.Offset(0, 1).Value
Me.ListBox1.List(0, 2) = cell.Offset(0, 2).Value
'End If
End If
Next cell

End Sub


Thanks for your help..

Abdul