CODEWITHSUNDEEP
vbaexcel
sbn
sbn

Reputation: 63

Loop through ListBoxes in Userform (Excel)

I'm trying to to loop through 10 Listboxes on Userform.

But when I'm running code below, I have error - type mismatch.

Listboxes are lstCecha01 to lstCecha10.

Code is:

Dim arrtabelaCechGrupy() As MSForms.ListBox
arrtabelaCechGrupy = Array(Me.lstCecha01, Me.lstCecha02, Me.lstCecha03, Me.lstCecha04, Me.lstCecha05, Me.lstCecha06, _
                           Me.lstCecha07, Me.lstCecha08, Me.lstCecha09, Me.lstCecha10)

Dim LBox As Variant

For Each LBox In arrtabelaCechGrupy

Next LBox

Thank you for help.

Upvotes: 0

Views: 726

Answers (1)

DisplayName
DisplayName

Reputation: 13386

you have to declare your arrtabelaCechGrupy() array as of Variant type

Private Sub CommandButton1_Click()
    Dim arrtabelaCechGrupy() As Variant

    arrtabelaCechGrupy = Array(Me.lstCecha01, Me.lstCecha02, Me.lstCecha03, Me.lstCecha04, Me.lstCecha05, Me.lstCecha06, Me.lstCecha07, Me.lstCecha08, Me.lstCecha09, Me.lstCecha10)

    Dim LBox As Variant

    For Each LBox In arrtabelaCechGrupy
        MsgBox LBox.name
    Next LBox
End Sub

but you could avoid array and address listboxes by their name via Userform Controls collection

Private Sub CommandButton1_Click()
    Dim iLB As Long

    For iLB = 1 To 10
        With Me.Controls("lstCecha" & Format(iLB, "00"))
            MsgBox .name
        End With
    Next
End Sub

Upvotes: 1

Related Questions