Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a listbox to show every unique record in a range | Excel Worksheet Functions | |||
Q: find range, listbox - format and fit columns | Excel Programming | |||
listbox not dsplaying all records in a named range | Excel Programming | |||
ComboBox Population of Cell Range | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming |