We help IT Professionals succeed at work.

Access VBA loop through Multi Select ListBox

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

What Access VBA code would I use to loop through the selected items in the following ListBox and get the values for Manufacturer and Country?

1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
You can use the ItemsSelected property to return a collection of the indices of the selected rows.

You can loop through that collection like this.
Dim idx As Variant

    For Each idx In Me!ListBox1.ItemsSelected
        ' do stuff with selected rows 
    Next idx

Open in new window

Within the loop you can use the Column/ItemData properties to access the actual data.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks. How do I refer to a specific field like Manfacturer in that loop?
Analyst Assistant
Commented:
Here's an example which should populate an array with the selected manufacturers.
Dim idx As Variant
Dim arrManufacturers As Variant
Dim cnt As Long
 
    With Me!ListBox1

        ReDim arrManufacturers(1 To .ListCount)

        For Each idx In .ItemsSelected
            cnt = cnt+1
            arrManufacturers(cnt) = .Column(2, idx)
        Next idx
         
    End With

    ReDim Preserve  arrManufacturers(1 To cnt)

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much for the help