Array as Source for List Box

[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.

 

[Dave Peterson]

Rowsource requires a range.

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

Where myArray is your array.

 

[lesleyann76]

Rowsource requires a range.

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

Where myArray is your array.

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.

 

[Dave Peterson]

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)

 

[lesleyann76]

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)

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