ListBox population from Excel range

[Tim Coddington]

Ok. Figured out how to load a list box
from an Excel range, like so ...

Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 4
ListBox1.RowSource = "A2" & Cells(1, 1).End(xlDown).Row
End Sub

Sample of spreadsheet values follows:
Name, First
Name, Last
Class
Address

Amber
Boger
2
(e-mail address removed)

Shane
Bobber
1
(e-mail address removed)

Sean
Cantral
1
(e-mail address removed)

Bobby
Smith
3
(e-mail address removed)

Lucy
Johnson
1
(e-mail address removed)

Linn
Cenad
2
(e-mail address removed)



Now I need to load ListBox1 from the same range, but only data
from a certain class, say 2. The list box would pick up Amber and
Linn, but not Sean (because he is class 1). Is there a way to do that?

 

[Tim Coddington]

OK. So the nice columns I had turned into rows. Sorry.

 

[Dick Kusleika]

Tim

For that you should use the AddItem method instead of the RowSource
property. Basically, like this

For Each cell in MyRange
If cell.Offset(0,2).Value = 2 'Class
Me.ListBox1.AddItem cell.Value
Me.ListBox1.List(Me.ListBox1.ListCount-1,1) = cell.Offset(0,1).Value
'2nd column
'etc. for the 3rd and 4th columns
End If
Next cell

See also here http://www.dicks-blog.com/excel/2004/05/populating_acti.html
and here http://www.dicks-blog.com/excel/2004/05/populating_mult.html

 

[Ben Palmer]

When the form is initialised, use the Clear Method to clear the list
box and then loop through your database and use the AddItem Method to
add data to the listbox if the class field meets your criteria.

 

[Tim Coddington]

Thanks Dick! Thanks Ben!
This also helps me understand how to use List and ListCount to
populate the second, third, and fourth columns.
Great news group!