List Box on worksheet

[Stuart]

Working with a List Box that is situated on a worksheet, (NOT in a Dialog or UserForm)

how do I get the entries selected when on MultiSelect?

 

[Tom Ogilvy]

Same as when located on a Userform

Private Sub CommandButton1_Click()
With Worksheets("sheet1")
For i = 0 To .ListBox1.ListCount - 1
If .ListBox1.Selected(i) Then
sStr = sStr & .ListBox1.List(i, 0) & vbNewLine
End If
Next
End With
MsgBox sStr

End Sub


--
Regards,
Tom Ogilvy


Working with a List Box that is situated on a worksheet, (NOT in a Dialog or
UserForm)

how do I get the entries selected when on MultiSelect?

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
Dim lbox As MSForms.ListBox
Set lbox = Worksheets("jpegs"). _
OLEObjects("ListBoxMain").Object
For i = 0 To lbox.ListCount - 1
If lbox.Selected(i) Then
sStr = sStr & lbox.List(i, 0) & vbNewLine
End If
Next

MsgBox sStr

End Sub

 

[Stuart]

I just cannot get this to work, the only thing that I can do is select it
with the following line

Worksheets("jpegs").Shapes("ListBoxMain").Select

I cannot refer to it as **Worksheets("jpegs").ListBoxMain**

 

[Stuart]

Hi Tom,
I still can't get your code to work! I get the run time error 1004 "Unable
to get the OLEobjets properties of the worksheet class " message at the "Set
lbox" line

I messed around for ages and finally got the following to work, however, I
am sure your suggestions is the proper way to do it, and I would like to
understand where I am going wrong.

Sub GetVal()
Dim lBox, dePart1
Set lBox = Sheets("Sheet4").ListBoxes("List Box 1")
dePart1 = ""
With lBox
For i = 1 To .ListCount
If .Selected(i) Then
dePart1 = dePart1 & .List(i) & ":::"
End If
Next i
End With
Range("C1") = dePart1
End Sub

 

[Dave Peterson]

There are two different listboxes you can add to your worksheet.

One comes from the ControlToolbox toolbar (OLEObject stuff) and one comes from
the Forms toolbar (.listboxes("list box 1") stuff).

Tom's code will work for the ControlToolbox listbox.

Your code will work for the Forms toolbar listbox.