ListBox List Code?

  • Thread starter Thread starter Abdul
  • Start date Start date
A

Abdul

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
 
You're updating the 2nd and third columns of the first entry with these lines:
Me.ListBox1.List(0, 1) = cell.Offset(0, 1).Value
Me.ListBox1.List(0, 2) = cell.Offset(0, 2).Value
Click to expand...

Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = cell.Offset(0, 1).Value
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = cell.Offset(0, 2).Value

To save typing:
With Me.ListBox1
.AddItem cell.Value
.List(.ListCount - 1, 1) = cell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
End With

And John Walkenbach shows a technique for getting a unique list into a
listbox--you can use the same kind of thing for your combobox.

http://j-walk.com/ss/excel/tips/tip47.htm
 
Back
Top