Simplify code

[Emperor]

Hi all,

I have 10 listboxes in my Userform and want to check if there is at least 1 item selected. I currently use this code, but can this be a loop instead of 10 different pieces of code?

Dim i As Integer
Dim count As Integer


'Categorie
count = 0
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            count = count + 1
        End If
    Next i
If count = 0 Then
MsgBox ("You have to select at least 1 item!")
Exit Sub
End If

Thanks in advance!

Mathijs.

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] AreThereEmptyListBoxes()

    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] ctl [COLOR="Blue"]As[/COLOR] MSForms.Control
    [COLOR="Blue"]Dim[/COLOR] lb [COLOR="Blue"]As[/COLOR] MSForms.ListBox
    
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] ctl [COLOR="Blue"]In[/COLOR] UserForm1.Controls
        [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]TypeOf[/COLOR] ctl [COLOR="Blue"]Is[/COLOR] MSForms.ListBox [COLOR="Blue"]Then[/COLOR]
            lb = ctl
            [COLOR="Blue"]If[/COLOR] lb.ListIndex > -1 [COLOR="Blue"]Then[/COLOR] i = i + 1
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]Next[/COLOR]
    
    [COLOR="Blue"]If[/COLOR] i > 0 [COLOR="Blue"]Then[/COLOR]
        MsgBox i & " listboxes have(s) selected value."
    [COLOR="Blue"]Else[/COLOR]
        MsgBox "There are no listboxes which have selected value."
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Emperor]

Hi Sektor,

You code errors at 'lb = ctl'

If I read you code correctly it checks if there is an selected item in 1 of the listboxes? While I need it to check if there is 1 item selected in each of the listboxes.

Thanks for your help.

p.s.
I am using Excel 2003

 

[Sektor]

Sub AreThereEmptyListBoxes()

    Dim i As Integer
    Dim ctl As MSForms.Control
    Dim lb As MSForms.ListBox
    
    For Each ctl In UserForm1.Controls
        If TypeOf ctl Is MSForms.ListBox Then
            [B][COLOR="Red"]Set[/COLOR][/B] lb = ctl
            If lb.ListIndex > -1 Then i = i + 1
        End If
    Next
    
    If i > 0 Then
        MsgBox i & " listboxes have(s) selected value."
    Else
        MsgBox "There are no listboxes which have selected value."
    End If

End Sub

 

[Emperor]

Sektor, thanks for your quick reply.

When I run your new code it allways says there are 9 filled listboxes (even when I select nothing)

I my previous post correct?

Gr. Mathijs.