Iterate listbox rows

D

dave h

Hi,

I need to interate thru all the rows of a list box I just loaded. The first
field in table1 is an integer.
When I run this I get a type mismatch: (without the for...each it runs fine)

Dim myList as Control
Set myList = Me!aListBox
myList.RowSource = "Select * FROM table1 "
myList = myList.ItemData(0)
Dim firstField As Variant
For Each firstField In myList.Column(0) ' blows up here with type
mismatch
Next firstField

Any suggestions on what I'm doing wrong - thanks, Dave H
 
D

dave h

The following code does work, but still would like to know why the "for
each" does not:

myList.RowSource = "Select * FROM table1 "
Dim rowCount As Integer
Dim i As Integer
rowCount = myList.ListCount
For i = 0 To rowCount - 1
myList = myList.ItemData(i)
Debug.Print myList.Column(0)
Next i
 
P

Paul Overway

The itemdata in a list box isn't a collection...it is an array, hence no For
Each. ItemsSelected is a collection though.
 
D

dave h

Thanks Paul,

I wasn't aware of that distinction between Collection and Array.
Is there some collection property of a list box (like ItemsSelected) that
will yield all rows - selected or not selected?

Thanks, Dave H.
 
P

Paul Overway

No, there is only the ItemsSelected collection...just iterate through the
ItemData like you are. If you have column headers turned on, you should
start your For at 1 instead of 0 though.
 
Top