ListBox population from Excel range
 

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:D" & Cells(1, 1).End(xlDown).Row
End Sub

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

Amber
Boger
2


Shane
Bobber
1


Sean
Cantral
1


Bobby
Smith
3


Lucy
Johnson
1


Linn
Cenad
2




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?

ListBox population from Excel range
 

OK. So the nice columns I had turned into rows. Sorry.
"Tim Coddington" wrote in message
...
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:D" & Cells(1, 1).End(xlDown).Row
End Sub

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

Amber
Boger
2


Shane
Bobber
1


Sean
Cantral
1


Bobby
Smith
3


Lucy
Johnson
1


Linn
Cenad
2




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?

ListBox population from Excel range
 

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...ting_acti.html
and here http://www.dicks-blog.com/excel/2004...ting_mult.html


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


Tim Coddington wrote:
OK. So the nice columns I had turned into rows. Sorry.
"Tim Coddington" wrote in message
...
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:D" & Cells(1, 1).End(xlDown).Row
End Sub

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

Amber
Boger
2


Shane
Bobber
1


Sean
Cantral
1


Bobby
Smith
3


Lucy
Johnson
1


Linn
Cenad
2




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?

ListBox population from Excel range
 

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.

Thanks! ListBox population from Excel range
 

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!
"Dick Kusleika" wrote in message
...
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...ting_acti.html
and here http://www.dicks-blog.com/excel/2004...ting_mult.html


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


Tim Coddington wrote:
OK. So the nice columns I had turned into rows. Sorry.
"Tim Coddington" wrote in message
...
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:D" & Cells(1, 1).End(xlDown).Row
End Sub

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

Amber
Boger
2


Shane
Bobber
1


Sean
Cantral
1


Bobby
Smith
3


Lucy
Johnson
1


Linn
Cenad
2




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?