Array as Source for List Box

  • Thread starter Thread starter Coby
  • Start date Start date
C

Coby

Does anyone know if it is possible to set the row source for a list
box to an array rather than a range of cells?


I am mainly trying to filter out certain rows from the list box.

I Access it is simple because an Sql query with a Where clause does
the trick by committing a recordset much like an array to the list
box.

I am not sure how to do this using the list box object in Excel,
however.

I had also thought of just going with the range of cells rather than
an array if I had to, then just running a loop to remove items from
the list?

I tried both scenarios and have had no luck.

Thanks for sharing any knowledge you may have on this.

Coby.
 
Rowsource requires a range.

But you could use:
me.listbox1.list = myArray

Where myArray is your array.
 
Rowsource requires a range.

But you could use:
me.listbox1.list = myArray

Where myArray is your array.
Click to expand...

Great! I wasn't even really clear on the ways to populate the list
box.
Consequently, I have only been using the row source property and was
unaware of other ways to fill that list.

I am going give that a shot now.

Thank, Dave
Coby.
 
You could loop through the range, too.

dim myCell as range
dim myRng as range
set myrng = worksheets("sheet999").range("a1:a13")

for each mycell in myrng.cells
if mycell.value > 10 then
me.listbox1.additem mycell.value
end if
next mycell

(for instance)
 
You could loop through the range, too.

dim myCell as range
dim myRng as range
set myrng = worksheets("sheet999").range("a1:a13")

for each mycell in myrng.cells
if mycell.value > 10 then
me.listbox1.additem mycell.value
end if
next mycell

(for instance)
Click to expand...

I like that even better. I just tested that out an will work great
for what I am doing.
Thanks, again.
Coby.
 
Back
Top