Excel 2007 - Listbox "Selected()" property fails

[GeorgeBro]

I am having a problem trying to select items from a listbox that is drawn on my worksheet. I have no problem adding items, or removing items.... only when I try to identify the items that are selected.

There is no ".Selected" in the intellisense for either the object directly, or the object.ControlFormat. When I execute this code, Excel fails with the error message: "Runtime Error 438. Object doesn't support this property or method."

I've scoured the net looking for help, but couldn't find any that addressed this issue.

Anybody have insight on how to either fix the problem or a workaround where I can see what items are selected and dump them into a collection?

' I tried all three modes of the listbox (single, multi, extended - select), which is available when right-clicking the control.

    ' Dimension variables and objects
        Dim ws As Excel.Worksheet
        Dim lst As Shape
        Dim cList As Collection         ' The index of the "Selected" currencies
        Dim i As Integer                ' Loop variable
 
    ' Set objects
        Set ws = Application.Sheets(MAIN_SHEET)
        Set lst = ws.Shapes("lstCurrencies")
        Set cList = New Collection
 
    ' Set the Selected Items
        With lst.ControlFormat
            For i = 1 To .ListCount
                If .Selected(i) Then            ' <----  FAILS HERE
                    cList.Add lst.ControlFormat.List(i)
                    .Selected(i) = False
                End If
            Next i
        End With
 

[Guido Geurs]

dear,

this works in Office 2003:

Private Sub CommandButton2_Click()
Dim i As Integer
Dim data As String
   For i = 0 To ListBox1.ListCount - 1
      If ListBox1.Selected(i) Then data = data & ListBox1.List(i) & "-"
   Next
   TextBox1.Text = data
End Sub

see attachment

br,

[GeorgeBro]

Yes, but unfortunately not in the version I'm using (2007). Maybe it's a library I don't have installed (or the order that the libraries are listed)? Or just reduced functionality or transversed into something else? Or maybe the way I dim'd the object as shape? Not sure, but it's driving me nuts! And thanks for all your help recently with everything, much appreciated.

[Guido Geurs]

dear,

Is my attachment running in 2007 ?

br,

[GeorgeBro]

Ahh, I didn't even see the attachment, and yes it is working!

I see that you used the ActiveX listbox, and not the Form listbox. The ActiveX listbox methods were available in the code-behind for the sheet, but for me I was unable to access it in a module.

I have a few things to work backwards from to get things working properly now.

Thanks ggeu, you've saved the rest of my hair, as I didn't get a chance to pull all of it out yet!

[GeorgeBro]

Well I don't know what it was, but I still couldn't get it to work in my workbook. It worked in a new XL07 workbook, but not the one I was working on.

What I did was export all the modules, form and code to a new workbook, testing the functionality between each import.

And it works in the new workbook that contains all of the code and copied sheets.

I notice that there are two dll libraries that I didn't reference in the new workbook, so maybe it's either one of those that was conflicting, or it was a setting in the options of the workbook. Who knows, but without your help I would have never figured this one out.

Thanks again!