Storing Multiple User Inputs from a ListBox using Excel VBA

Question

I am trying to have the user select multiple options from an ActiveX ListBox (ListBoxTest1) and then have their selections stored in separate rows on another sheet. I keep getting the error "Unable to get the ListBoxes property of the Worksheet class" when the first line shown here is about to be executed.

Set listX = Sheets("Availability Criteria").ListBoxes("ListBoxTest1")

With listX
    For i = 1 To .ListCount
        If .Selected(i) Then
            Sheets("WorkingSheet").Cells(i + 4, "F").Value = .Items(i)
        End If
    Next i
End With

I thought the .Items property was what I needed to take the input from the ListBox, but perhaps that's the illegal property in question. All of my code is in the modules section, not a private sub.

I'm still very new to Excel's VBA....please help.

Comintern Comintern · Accepted Answer · 2016-10-28T22:29:23

A Worksheet actually doesn't have a ListBoxes property natively, and Sheets("Availability Criteria") returns a strongly typed Worksheet. Probably the simplest way to access the collection is to late-bind the Worksheet:

Dim sh As Object
Set sh = Sheets("Availability Criteria")
Set listX = sh.ListBoxes("ListBoxTest1")

EDIT

The above code only applies to Form Controls. For ActiveX controls, you need to access them via the OLEObjects collection:

Dim listX As MSForms.ListBox
Set listX = Sheets("Availability Criteria").OLEObjects("ListBoxTest1").Object

With listX
    For i = 1 To .ListCount
        If .Selected(i) Then
            Sheets("WorkingSheet").Cells(i + 4, "F").Value = .List(i)
        End If
    Next i
End With

Storing Multiple User Inputs from a ListBox using Excel VBA

1 Answers