Form Control List Box problem with listcount

Andy

For your reference i copied entire code - Note the form list box is in sheet ISIP

Public Sub CommandButton1_Click()
'Sheets("ISIP").objLB.RemoveAllItems

'Sheets("ISIP").ListBoxes("List Box 45").RemoveAllItems
'Sheets("ISIP").ListBoxes("List Box 45").AddItem Sheets("2011").Range("T" & 5)
'Sheets("ISIP").ListBoxes("List Box 45").List = Sheets("2011").Range("T" & 5)
'Sheets("ISIP").ListBox2.Clear
'Sheets("ISIP").ListBox3.Clear
Sheets("ISIP").ListBox4.Clear

If ListBox2 <> "ALL" Then Range("Q31") = ""

If ListBox2 = "ALL" Then

Sheets("ISIP").Range("Q31") = "ALL"
If ListBox3 = "ALL" Then

For i = 2 To Sheets("2011").Range("A2").End(xlDown).Row
    If Sheets("2011").Range("C" & i) = ListBox1.Text Then
        Sheets("ISIP").objLB.AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

End If

If ListBox3 = "GENERAL" Then

For i = 2 To Sheets("2011").Range("A2").End(xlDown).Row
    If Sheets("2011").Range("C" & i) = ListBox1.Text And Sheets("2011").Range("BB" & i) = "" And Sheets("2011").Range("BC" & i) = "" Then
        Sheets("ISIP").ListBoxes("List Box 45").AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

End If

If ListBox3 = "SE" Then

For i = 2 To Sheets("2011").Range("A2").End(xlDown).Row
    If Sheets("2011").Range("C" & i) = ListBox1.Text And Sheets("2011").Range("BB" & i) <> "" Then
        Sheets("ISIP").objLB.AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

End If

If ListBox3 = "LEP" Then

For i = 2 To Sheets("2011").Range("A2").End(xlDown).Row
    If Sheets("2011").Range("C" & i) = ListBox1.Text And Sheets("2011").Range("BC" & i) <> "" Then
        Sheets("ISIP").objLB.AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

End If

If ListBox3 = "ED" Then

For i = 2 To Sheets("2011").Range("A2").End(xlDown).Row
    If Sheets("2011").Range("C" & i) = ListBox1.Text And Sheets("2011").Range("BP" & i) = "Y" Then
        Sheets("ISIP").objLB.AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

GoTo ENDS:

End If

End If


If ListBox3 = "ALL" Then

For i = 2 To Sheets("2011").Range("A2").End(xlDown).Row
    If Sheets("2011").Range("C" & i) = ListBox1.Text And Sheets("2011").Range("I" & i) = ListBox2.Text Then
        Sheets("ISIP").objLB.AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

End If

If ListBox3 = "GENERAL" Then

For i = 2 To Sheets("2011").Range("A2").End(xlDown).Row
    If Sheets("2011").Range("C" & i) = ListBox1.Text And Sheets("2011").Range("I" & i) = ListBox2.Text And Sheets("2011").Range("BB" & i) = "" And Sheets("2011").Range("BC" & i) = "" Then
        Sheets("ISIP").objLB.AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

End If

If ListBox3 = "SE" Then

For i = 2 To Sheets("2011").Range("A2").End(xlDown).Row
    If Sheets("2011").Range("C" & i) = ListBox1.Text And Sheets("2011").Range("I" & i) = ListBox2.Text And Sheets("2011").Range("BB" & i) <> "" Then
        Sheets("ISIP").objLB.AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

End If

If ListBox3 = "LEP" Then

For i = 2 To Sheets("2011").Range("A2").End(xlDown).Row
    If Sheets("2011").Range("C" & i) = ListBox1.Text And Sheets("2011").Range("I" & i) = ListBox2.Text And Sheets("2011").Range("BC" & i) <> "" Then
        Sheets("ISIP").objLB.AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

End If

If ListBox3 = "ED" Then

For i = 2 To Sheets("2011").Range("A2").End(xlDown).Row
    If Sheets("2011").Range("C" & i) = ListBox1.Text And Sheets("2011").Range("I" & i) = ListBox2.Text And Sheets("2011").Range("BP" & i) = "Y" Then
        Sheets("ISIP").objLB.AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").objLB.List(Sheets("ISIP").objLB.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

End If
ENDS:


UserForm1.Hide

End Sub

Private Sub ListBox2_Click()

End Sub

Public Sub UserForm_Activate()

Dim objLB As Shape
    Dim lngIndex
    
    Set objLB = Sheets("ISIP").Shapes("List Box 45")
    With objLB
        Debug.Print "Name", .Name
        With .ControlFormat
            Debug.Print "ListCount", .ListCount
            Debug.Print "ListIndex", .ListIndex
            For lngIndex = 1 To .ListCount
                Debug.Print , lngIndex, .List(lngIndex)
            Next
        End With
    End With


'Dim test1 As String
'Dim lb As ListBox
'Set lb = Sheets("ISIP").ListBoxes("List Box 45")


ListBox1.Clear
ListBox2.Clear
ListBox3.Clear

For i = 2 To Sheets("Grade and School List").Range("A1").End(xlDown).Row
ListBox1.AddItem Sheets("Grade and School List").Range("A" & i)
Next i

For i = 2 To Sheets("Grade and School List").Range("B1").End(xlDown).Row
ListBox2.AddItem Sheets("Grade and School List").Range("B" & i)
Next i

ListBox3.AddItem "ALL"
ListBox3.AddItem "GENERAL"
ListBox3.AddItem "LEP"
ListBox3.AddItem "SE"
ListBox3.AddItem "ED"

End Sub

Private Sub UserForm_Click()

End Sub