List Box on worksheet
 

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?

List Box on worksheet
 

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


Stuart wrote in message
...
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?

List Box on worksheet
 

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

--
Regards,
Tom Ogilvy




Stuart wrote in message
...


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

Tom Ogilvy wrote in message
...
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


Stuart wrote in message
...
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?

List Box on worksheet
 

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

Tom Ogilvy wrote in message
...
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


Stuart wrote in message
...
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?

List Box on worksheet
 

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



Tom Ogilvy wrote in message
...
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

--
Regards,
Tom Ogilvy




Stuart wrote in message
...


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

Tom Ogilvy wrote in message
...
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


Stuart wrote in message
...
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?

List Box on worksheet
 

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.



Stuart wrote:

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

Tom Ogilvy wrote in message
...
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

--
Regards,
Tom Ogilvy




Stuart wrote in message
...


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

Tom Ogilvy wrote in message
...
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


Stuart wrote in message
...
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?







--

Dave Peterson