Form Control List Box problem with listcount

**sthiru** · 09-04-2012, 03:46 PM

Hi Everyone

I'm trying to change "activex control listbox" to "form control list" box but I can't able to perform some vba operations. Such as listcount...etc and below is my code for activex listbox and I need to change it to form control list box. Name of the list box :List Box 45"

Please assist me

Public Sub CommandButton1_Click()
Sheets("ISIP").ListBox1.Clear
'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").ListBox1.AddItem Sheets("2011").Range("E" & i)
        Sheets("ISIP").ListBox1.List(Sheets("ISIP").ListBox1.ListCount - 1, 1) = Sheets("2011").Range("T" & i)
        Sheets("ISIP").ListBox1.List(Sheets("ISIP").ListBox1.ListCount - 1, 2) = Sheets("2011").Range("U" & i)
    End If
Next i

End If

**Andy Pope** · 09-05-2012, 04:31 AM

You need to use the ControlFormat object of the shape in order to get to the usual List properties

Sub X()

    Dim objLB As Shape
    Dim lngIndex
    
    Set objLB = ActiveSheet.Shapes("List Box 1")
    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
        
End Sub

**sthiru** · 09-05-2012, 11:02 AM

Andy thank you so much for your reply but I don't how to modify with my code could you please help me on this.

Please do reply

Thanks
Thiru

**Andy Pope** · 09-05-2012, 11:08 AM

Well what have you tried so far?

**sthiru** · 09-05-2012, 11:12 AM

Andy

What I have done is I have inputed your code in "UserForm_Activate()" and modified the other command codes with "objLb" for List box(e.g. Sheets("ISIP").objLb.List(Sheets("ISIP").objLb.ListCount - 1, 1) = Sheets("2011").Range("T" & i)

Please help me on this.

Thanks
Thiru

**sthiru** · 09-05-2012, 11:23 AM

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

**Andy Pope** · 09-05-2012, 11:23 AM

Before you go any further with this you know Form listbox does not have multiple columns?

**sthiru** · 09-05-2012, 11:28 AM

I there any other way that I can use multiple column as like listbox - what I meant is anyother controls other than activex.

**Andy Pope** · 09-05-2012, 11:36 AM

Not on the work sheet.

Depending on the content of the listbox you could concatenate the text per row.

Form Control List Box problem with listcount

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Form Control List Box problem with listcount

Re: Form Control List Box problem with listcount

Re: Form Control List Box problem with listcount

Re: Form Control List Box problem with listcount

Re: Form Control List Box problem with listcount

Re: Form Control List Box problem with listcount

Re: Form Control List Box problem with listcount

Re: Form Control List Box problem with listcount

Re: Form Control List Box problem with listcount

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions