Getting selected items in embedded listbox

[jrdnoland]

in another module I'm adding the lisbox. Trying to get the selected items is being a pain, I'm using this:

  Dim ItemReq(10) As String

  Dim lItem As Integer
  Dim objOLE As OLEObject, objListBox As msforms.ListBox
  Dim lstComponents As msforms.ListBox
  Dim ashp As Shape
  Dim a As Integer
  
  
  For Each ashp In Sheet2.Shapes
      If ashp.Name <> "lstComponents" Then
      Else
          For lItem = 0 To Sheet2.lstComponents.ListCount - 1
              If Sheet2.lstComponents.Selected(lItem) Then
                  ItemReq(a) = Sheet2.lstComponents.List(lItem)
                  a = a + 1
              End If
          Next
          Exit For
      End If
  Next ashp
  a = a - 1

I get the error Method or data member not found.

How would I resolve this?

See this post:

Delete worksheet activex listbox


This listbox is being filled this way:

Set objOLE = wsName.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=210, Top:=40, Width:=210, Height:=lstHeight)
    
' set some properties
With objOLE
    ' these are properties of the container OLEObject, not the Listbox itself
    .Name = "lstComponents"
    .ListFillRange = "C3:C" & rcnt + 2
    Set objListBox = .Object
    ' toggle visibility to ensure the control is clickable
    .Visible = False
    .Visible = True
End With
' now we can set the listbox-specific properties
With objListBox
    .ListStyle = fmListStyleOption
    .MultiSelect = fmMultiSelectMulti
    .MatchEntry = fmMatchEntryComplete
End With

[paulked]

You added the listbox to sheet 4, have you changed it to sheet 2?

[Jan Karel Pieterse]

Please note that if you're adding an ActiveX control to a sheet in the same workbook as where your code is running, you reset the running VBA project (and all of its module-level variables).
Wouldn't it be easier to use the listbox from the listboxes collection? Those are less error-prone.

[paulked]

Thank you for that info JKP, very good to know.