ListBox List Code?
 

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

ListBox List Code?
 

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

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



Abdul wrote:

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

--

Dave Peterson