Loop through multiselect listboxes for selected items

[ImSimple]

I have a form with many multiselect listboxes on it. I want to loop through each list box and get the selected items to copy to excel. I am getting a casting error "must be less than infinity"

For Each box As Object In Me.Controls '****INFINITY ERROR*****
If TypeOf box Is ListBox AndAlso Name.Contains(head.Name) Then
'If box.Name.Contains(head.Name) Then
Dim itms As ListBox.SelectedObjectCollection
itms = box.SelectedItems
excel.ActiveCell.Value2 = itms
excel.ActiveCell.Offset(+1, 0).Select()
'End If
End If
Next

Jacque

 

[Sorwen]

I've never had that error, though I've had forms with lots of controls on them, so I can only give you a maybe solution. What you can do is keep a list of control types and then iterate only through those. Then you could do this:

    Dim ListBoxList As New Collection

    Private Sub Form1_ControlAdded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ControlEventArgs) Handles MyBase.ControlAdded
        If TypeOf e.Control Is ListBox Then
            ListBoxList.Add(e.Control.Name)
        End If
    End Sub

Then just change your code as such:

        For Each ListBoxName As String In ListBoxList
            Dim CurrentLB As Object = Me.Controls(ListBoxName)
            If CurrentLB IsNot Nothing Then
                'If box.Name.Contains(head.Name) Then
                Dim itms As ListBox.SelectedObjectCollection
                itms = box.SelectedItems
                excel.ActiveCell.Value2 = itms
                excel.ActiveCell.Offset(+1, 0).Select()
                'End If
            End If
        Next

This is really just a tide you over until someone can help you with the actual error.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[ImSimple]

This gets me no errors but the items don't go to the sheet either. This is weird. Thanks for trying.

Jacque