PDA

View Full Version : Getting selected items in embedded listbox



jrdnoland
05-13-2020, 04:50 AM
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

(http://www.vbaexpress.com/forum/showthread.php?67339-Delete-worksheet-activex-listbox)
(http://www.vbaexpress.com/forum/showthread.php?67339-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



(http://www.vbaexpress.com/forum/showthread.php?67339-Delete-worksheet-activex-listbox)

paulked
05-13-2020, 06:33 AM
You added the listbox to sheet 4, have you changed it to sheet 2?

Jan Karel Pieterse
05-13-2020, 08:20 AM
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
05-13-2020, 08:32 AM
Thank you for that info JKP, very good to know.