CODEWITHSUNDEEP
excelvba
Bart g
Bart g

Reputation: 585

Object required Run-time error 424

I have a list of checkboxes and after some of them are checked I would like to know which ones are checked so I can work with those checked boxes. Not sure why these few lines don't work. After I execute it there is a pop up error message saying "Object required" Run-time error '424': and highlights line => ReDim SelectedItemArray(ListBox1.ListCount) As String. Yes I have four ListBoxes; ListBox1, ListBox2, ListBox3, ListBox4. Any help is appreciated. Thank you

Sub CheckedBoxes()

Dim SelectedItemArray() As String

ReDim SelectedItemArray(ListBox1.ListCount) As String

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
SelectedItemArray(i) = ListBox1.List(i)
End If
Next

End Sub

Upvotes: 1

Views: 3777

Answers (2)

David Zemens
David Zemens

Reputation: 53623

This is a function I use for ListBoxes on a UserForm. I modified it (further below) for use on Worksheet listboxes.

For form controls ListBox on a UserForm, call it like:

myArray = GetSelectedItems(ListBox1)

Here's the function which will accept any listbox from a UserForm as a named argument:

Public Function GetSelectedItems(lBox As MSForms.ListBox) As Variant
'returns an array of selected items in a ListBox
Dim tmpArray() As Variant
Dim i As Integer
Dim selCount As Integer

        selCount = -1
        For i = 0 To lBox.ListCount - 1
            If lBox.Selected(i) = True Then
                selCount = selCount + 1
                ReDim Preserve tmpArray(selCount)
                tmpArray(selCount) = lBox.List(i)

            End If
        Next
        If selCount = -1 Then
            GetSelectedItems = Array()
        Else:
            GetSelectedItems = tmpArray
        End If
End Function

If you are referring to a ListBox on a worksheet, try this instead:

Call it like this:

myArray = GetSelectedItems(Sheet1.Shapes("List Box 1").OLEFormat.Object)

Here's the function modified for Worksheet form control ListBox:

Public Function GetSelectedItems(lBox As Object) As Variant
'returns an array of selected items in a ListBox
Dim tmpArray() As Variant
Dim i As Integer
Dim selCount As Integer

        selCount = -1
        For i = 1 To lBox.ListCount - 1
            If lBox.Selected(i) = True Then
                selCount = selCount + 1
                ReDim Preserve tmpArray(selCount)
                tmpArray(selCount) = lBox.List(i)

            End If
        Next
        If selCount = -1 Then
            GetSelectedItems = Array()
        Else:
            GetSelectedItems = tmpArray
        End If
End Function

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

You need to fully qualifying the listbox. For example Sheet1.ListBox1.ListCount

Upvotes: 2

Related Questions