Help to populate listbox via vba

[Alexon2008]

I want to populate listbox with items from column c, but only if value in column j is "blue" and value in column k is "green".

I try to explain:
If value in cell j2 is "blue" and k2 is "green" then ad item to listbox from c2.
If value in cell j3 is "blue" and k3 is "green" then ad item to listbox from c3. and so on until row 200.

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, "J").Value = "blue" And .Cells(i, "K").Value = "green" Then

ListBox1.AddItem .Cells(i, "C").Value
End If
Next i
End With

End Sub

[Alexon2008]

xld thanks:thumb very much. You are very, very fast.

[lucas]

He is consistantly good and very fast. Few people I have ever known shared knowledge as generously as Bob.

[Aussiebear]

Well said Steve, Bob is, so I'm told, reluctant to promote himself as a MVP, but I am of the belief that he is inspirational in the knowlege he shares with us all. Sort of MVP of MVP's :clap2::clap2::clap2:

[Alexon2008]

Code does'nt work. Listbox don't get populate. I have listbox which is embedden in sheet. Please help!

[Bob Phillips]

Post your workbook.

[Alexon2008]

Here:

[Bob Phillips]

My code was for a userform, not an ACtiveX listbox.

This code will load the lisbox on workbbook open


Private Sub Workbook_Open()
Dim i As Long
Dim LastRow As Long

With Worksheets("Print")

LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, "J").Value = "blue" And .Cells(i, "K").Value = "green" Then

.OLEObjects("ListBox1").Object.AddItem .Cells(i, "C").Value
End If
Next i
End With

End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[Alexon2008]

Thanks it works. I have one more problems:
What to change in macro If Columns j, k and c with values are on sheet1 and listbox is on sheet 2.

[lucas]

Why not put your listbox on a userform and do away with the sheet with the listbox?

see attached with the first code that Bob posted incorporated.

[Bob Phillips]

Private Sub Workbook_Open()
Dim i As Long
Dim LastRow As Long

With Worksheets("Sheet2")

LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
For i = 1 To LastRow

If .Cells(i, "J").Value = "blue" And .Cells(i, "K").Value = "green" Then

Worksheets("Print").OLEObjects("ListBox1").Object.AddItem .Cells(i, "C").Value
End If
Next i
End With

End Sub