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)
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)