Help with Looping through Listboxes on a form 2

[tbl]

I have a Form called FormInput which I have populated with ListBoxes which the users have to select. I am looking for suitable code to loop through all of these listboxes and return the values of the boxes.
I have tried this as is suggested in my VBA book but it doesn't work.

Dim a As Integer
a = 38

For Each ListBox In Me.Controls
For i = 0 To ListBox.ListCount - 1
If ListBox.Selected(i) Then
Cells(a, 1).Value = ListBox.List(i, 0)
Cells(a, 2).Value = ListBox.List(i, 1)
a = a + 1
End If
Next i
Next

The thing that I am stuck with is how to loop through the ListBoxes. Is the ref. to Me.Controls maybe not the right one ?

Can anyone help please,

Richard

 

[stevexff]

Me.Controls doesn't only contain ListBoxes. You need to check the type of the control, and just Next if it's not a ListBox.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[tbl]

Thanks Steve,
How can I check if what is returned is a ListBox ?

Richard

 

[HarleyQuinn]

Something like:

Dim ctrl As Control

For Each ctrl In Me.Controls
If TypeOf ctrl Is ListBox Then
'Your code
End If
Next ctrl

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[tbl]

Thanks to both Steve and HarleyQuin for their help. My finished code looked like this..

Dim LBX As Control 'TBL
Dim a As Integer
a = 38

For Each LBX In Me.Controls
If TypeName(LBX) = "ListBox" Then
Dim LstBox As Variant
Set LstBox = LBX
For i = 0 To LstBox.ListCount - 1
If LstBox.Selected(i) Then
Cells(a, 1).Value = LstBox.List(i, 0)
Cells(a, 2).Value = LstBox.List(i, 1)
a = a + 1
End If
Next i
End If
Next LBX