List Box help
 

Hi

I have created a list box using the multi select option. (say 1 - 10)

If the user highlights several options say 1,7,9 can I return their
selection to cells A1 = 1,A2 = 7,A3=10 etc.

I have seen in a reference book that the selection can be displayed in Msg
box format but I can't work out how to transfer results to worksheet.

Thanks very much

Steve

With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
Range("A" & i).Value = .List(i)
End If
Next i
End With


--
HTH

Bob Phillips

"Steve Jones" wrote in message
...
Hi

I have created a list box using the multi select option. (say 1 - 10)

If the user highlights several options say 1,7,9 can I return their
selection to cells A1 = 1,A2 = 7,A3=10 etc.

I have seen in a reference book that the selection can be displayed in Msg
box format but I can't work out how to transfer results to worksheet.

Thanks very much

Steve

Thanks very much Bob for the speedy response.

I have copied the code and run it from a cmd button.

It is coming with an error "method range of object global failed".

I have no doubt it is something I have done.

I did substitute the "A" for "A1" afterwards. This worked although not quite
as planned, I selected 1,3 and 5 and it pasted the values into cells
A10,A12 and A14.

I'd be grateful for some more help.

Thanks

Steve







"Bob Phillips" wrote in message
...
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
Range("A" & i).Value = .List(i)
End If
Next i
End With


--
HTH

Bob Phillips

"Steve Jones" wrote in message
...
Hi

I have created a list box using the multi select option. (say 1 - 10)

If the user highlights several options say 1,7,9 can I return their
selection to cells A1 = 1,A2 = 7,A3=10 etc.

I have seen in a reference book that the selection can be displayed in
Msg
box format but I can't work out how to transfer results to worksheet.

Thanks very much

Steve

I think Bob had a small typo in his code:

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
me.Range("A" & j).Value = .List(i)
End If
Next i
End With
End Sub

As a side note, you may want to include the current version of the code you're
using and indicate the line that caused the trouble.

I assumed that the listbox and commandbutton were from the forms toolbar and
were placed on the worksheet.

If that's true and you're using xl97, make sure you change the .takefocusonclick
property of the commandbutton to false.

(Rightclick on that commandbutton while in design mode and choose properties to
find/change it.)

If this is on a userform, then I broke it.

Me.Range("a" & j).value = .list(i)
should be more like:
worksheets("sheet99").range("a" & j).value = .list(i)




Steve Jones wrote:

Thanks very much Bob for the speedy response.

I have copied the code and run it from a cmd button.

It is coming with an error "method range of object global failed".

I have no doubt it is something I have done.

I did substitute the "A" for "A1" afterwards. This worked although not quite
as planned, I selected 1,3 and 5 and it pasted the values into cells
A10,A12 and A14.

I'd be grateful for some more help.

Thanks

Steve

"Bob Phillips" wrote in message
...
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
Range("A" & i).Value = .List(i)
End If
Next i
End With


--
HTH

Bob Phillips

"Steve Jones" wrote in message
...
Hi

I have created a list box using the multi select option. (say 1 - 10)

If the user highlights several options say 1,7,9 can I return their
selection to cells A1 = 1,A2 = 7,A3=10 etc.

I have seen in a reference book that the selection can be displayed in
Msg
box format but I can't work out how to transfer results to worksheet.

Thanks very much

Steve






--

Dave Peterson

Just want to thank you both for the help - it does exactly what I want now.

Cheers

Steve
"Dave Peterson" wrote in message
...
I think Bob had a small typo in his code:

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
me.Range("A" & j).Value = .List(i)
End If
Next i
End With
End Sub

As a side note, you may want to include the current version of the code
you're
using and indicate the line that caused the trouble.

I assumed that the listbox and commandbutton were from the forms toolbar
and
were placed on the worksheet.

If that's true and you're using xl97, make sure you change the
.takefocusonclick
property of the commandbutton to false.

(Rightclick on that commandbutton while in design mode and choose
properties to
find/change it.)

If this is on a userform, then I broke it.

Me.Range("a" & j).value = .list(i)
should be more like:
worksheets("sheet99").range("a" & j).value = .list(i)




Steve Jones wrote:

Thanks very much Bob for the speedy response.

I have copied the code and run it from a cmd button.

It is coming with an error "method range of object global failed".

I have no doubt it is something I have done.

I did substitute the "A" for "A1" afterwards. This worked although not
quite
as planned, I selected 1,3 and 5 and it pasted the values into cells
A10,A12 and A14.

I'd be grateful for some more help.

Thanks

Steve

"Bob Phillips" wrote in message
...
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
Range("A" & i).Value = .List(i)
End If
Next i
End With


--
HTH

Bob Phillips

"Steve Jones" wrote in message
...
Hi

I have created a list box using the multi select option. (say 1 - 10)

If the user highlights several options say 1,7,9 can I return their
selection to cells A1 = 1,A2 = 7,A3=10 etc.

I have seen in a reference book that the selection can be displayed in
Msg
box format but I can't work out how to transfer results to worksheet.

Thanks very much

Steve






--

Dave Peterson

Hi Dave / Bob

Really sorry to come back again. The code is fine and does exactly as it
should. I am using a userform and have changed the code as you suggested. As
it was an example I thought I'd be able to alter the target range to any
cell.

I have substituted .range("A" & j) for .range("B10" & j) thinking the list
information would appear in cell B10. It is actually returning the
information in "B101". Similarly if I change "A" to "A1" it is returning the
information in "A11". How can I get it to return the selected data in cell
"B10"?

Thanks once again for you help

Steve




"Dave Peterson" wrote in message
...
I think Bob had a small typo in his code:

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
me.Range("A" & j).Value = .List(i)
End If
Next i
End With
End Sub

As a side note, you may want to include the current version of the code
you're
using and indicate the line that caused the trouble.

I assumed that the listbox and commandbutton were from the forms toolbar
and
were placed on the worksheet.

If that's true and you're using xl97, make sure you change the
.takefocusonclick
property of the commandbutton to false.

(Rightclick on that commandbutton while in design mode and choose
properties to
find/change it.)

If this is on a userform, then I broke it.

Me.Range("a" & j).value = .list(i)
should be more like:
worksheets("sheet99").range("a" & j).value = .list(i)




Steve Jones wrote:

Thanks very much Bob for the speedy response.

I have copied the code and run it from a cmd button.

It is coming with an error "method range of object global failed".

I have no doubt it is something I have done.

I did substitute the "A" for "A1" afterwards. This worked although not
quite
as planned, I selected 1,3 and 5 and it pasted the values into cells
A10,A12 and A14.

I'd be grateful for some more help.

Thanks

Steve

"Bob Phillips" wrote in message
...
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
Range("A" & i).Value = .List(i)
End If
Next i
End With


--
HTH

Bob Phillips

"Steve Jones" wrote in message
...
Hi

I have created a list box using the multi select option. (say 1 - 10)

If the user highlights several options say 1,7,9 can I return their
selection to cells A1 = 1,A2 = 7,A3=10 etc.

I have seen in a reference book that the selection can be displayed in
Msg
box format but I can't work out how to transfer results to worksheet.

Thanks very much

Steve






--

Dave Peterson

Do you always want to start at B10 or if you show the form again, do you want to
continue with the next empty cell under B10?

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long
Dim DestCell As Range

Set DestCell = Me.Range("B10")

'or the next empty cell under B10.
With Me
Set DestCell = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0)
If DestCell.Row < 10 Then
Set DestCell = .Range("b10")
End If
End With

With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
DestCell.Value = .List(i)
Set DestCell = DestCell.Offset(1, 0)
End If
Next i
End With
End Sub


Steve Jones wrote:

Hi Dave / Bob

Really sorry to come back again. The code is fine and does exactly as it
should. I am using a userform and have changed the code as you suggested. As
it was an example I thought I'd be able to alter the target range to any
cell.

I have substituted .range("A" & j) for .range("B10" & j) thinking the list
information would appear in cell B10. It is actually returning the
information in "B101". Similarly if I change "A" to "A1" it is returning the
information in "A11". How can I get it to return the selected data in cell
"B10"?

Thanks once again for you help

Steve

"Dave Peterson" wrote in message
...
I think Bob had a small typo in his code:

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
me.Range("A" & j).Value = .List(i)
End If
Next i
End With
End Sub

As a side note, you may want to include the current version of the code
you're
using and indicate the line that caused the trouble.

I assumed that the listbox and commandbutton were from the forms toolbar
and
were placed on the worksheet.

If that's true and you're using xl97, make sure you change the
.takefocusonclick
property of the commandbutton to false.

(Rightclick on that commandbutton while in design mode and choose
properties to
find/change it.)

If this is on a userform, then I broke it.

Me.Range("a" & j).value = .list(i)
should be more like:
worksheets("sheet99").range("a" & j).value = .list(i)




Steve Jones wrote:

Thanks very much Bob for the speedy response.

I have copied the code and run it from a cmd button.

It is coming with an error "method range of object global failed".

I have no doubt it is something I have done.

I did substitute the "A" for "A1" afterwards. This worked although not
quite
as planned, I selected 1,3 and 5 and it pasted the values into cells
A10,A12 and A14.

I'd be grateful for some more help.

Thanks

Steve

"Bob Phillips" wrote in message
...
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
Range("A" & i).Value = .List(i)
End If
Next i
End With


--
HTH

Bob Phillips

"Steve Jones" wrote in message
...
Hi

I have created a list box using the multi select option. (say 1 - 10)

If the user highlights several options say 1,7,9 can I return their
selection to cells A1 = 1,A2 = 7,A3=10 etc.

I have seen in a reference book that the selection can be displayed in
Msg
box format but I can't work out how to transfer results to worksheet.

Thanks very much

Steve






--

Dave Peterson

--

Dave Peterson