Using .Selected with ListBox Form Controls

Question

This has been driving me a little crazy -

I have a List Box on the a worksheet called MyListBox, the List Box is populated with data from a range. The List Box is multi select.

I want to use the following to get the selected items:

Private Sub MyListBox()

Dim lb As ListBox

Dim i As Long, temp

Set lb = Sheets("Sheet1").ListBoxes(1)

With lb

For i = 0 To .ListCount

If .Selected(i) Then

temp = temp & i & " " & .List(i) & Chr(10)

End If

Next i

End With

MsgBox "Selected items were:" & Chr(10) & temp

End Sub

The problem is that I don't think I can use Selected with ListBox objects. I've tried across Excel 2010 (PC) and Mac (2011). I need to use Form Control elements as the sheet has to be shared between Mac + PC users.

Any ideas as to how to get the selected items?

Is the documentation really as bad as I think it is? Normally I would go to a docs page and just look up the methods for an object, but there's just nothing!

Thanks -

Answer 1

Now Solved

It seems that in Mac VBA land you have to use:

ActiveSheet.Shapes("MyListBox").OLEFormat.Object.Selected(i)

With lb

For i = 1 To .ListCount

If ActiveSheet.Shapes("MyListBox").OLEFormat.Object.Selected(i) = True Then

temp = temp & .List(i) & Chr(10)

End If

Next i

End

Then it works.

Answer 2

Yes, I saw the subscript error, but it wasn't relevant to the real problem.

Answer 3

Ok - so now looking at the code again, I have got it to work on Excel 2010 (PC), the error was here:

For i = 0 To .ListCount

Should read:

For i = 1 To .ListCount

So if I save on PC, Open on Excel 2011, I get the error:

Method 'Selected' of object 'ListBox' failed

Answer 4

Thanks Bob

It's odd that they don't work on the PC either. The docs for the PC version say that you should only use multiple select if you intend to use the control with VBA, but you can't it seems. Does this mean the functionality has been broken for some time.

When searching for a solution there are lots of ideas that use .Selection but I guess they must be using PC ActiveX controls and not forms.

Thanks for the idea, but I need this to be a multi-selct list that I'm then going to use to control a Pivot Table on another worksheet. All this wouldn't be an issue if Slicers were implemented in 2011!

Answer 5

Maybe JE will chime in to add to this. As far as I remember, multi-select list boxes never did work properly on the Mac. I see nothing wrong with your code, and selected should indeed be a property of the list as it is indicated in the object browser. And, I agree, that the documentation leaves a whole lot to be desired. A really clunky way to do this, but one that wouold work on both platforms, is to use a single selection list box within a loop to ask the user if there is anything else. Then you can build your own list of selected items.