Solved: Add ListBox selections to specific cell range

[cgannm]

Hi, can you help modify code to add ListBox selections in range B5:B15 only.
Also, I would like for ListBox to append a new selection within this range. This code puts new selections starting in B5 and overwrites selections already made.

Thanks,
cgannm


[VBA]
Private Sub CommandButton2_Click()
Dim lItem As Long
Dim cell As Range
Set cell = ActiveSheet.Range("B5")
For lItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem) = True Then
cell.Value = ListBox1.List(lItem)
Set cell = cell.Offset(1, 0)

ListBox1.Selected(lItem) = False
End If
Next
End Sub
[/VBA]

[mdmackillop]

Is this what you're after?
[VBA]Private Sub CommandButton2_Click()
Dim lItem As Long
Dim Rng As Range
Set Rng = ActiveSheet.Range("B5:B15")
i = 1
For lItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem) = True Then
Do
If Rng(i) = "" Then
Rng(i).Value = ListBox1.List(lItem)
Exit Do
End If
i = i + 1
Loop Until i > Rng.Cells.Count
ListBox1.Selected(lItem) = False
End If
Next

End Sub
[/VBA]

[Kenneth Hobs]

The 2nd line is to insure that your input will be put in B5 if no data occurs from B5 and down.

[vba]Set cell = ActiveSheet.Range("B" & Rows.Count).End(xlUp)
If cell.Row < 5 Then Set cell = Range("B5")[/vba]

[cgannm]

Thanks! This work great.

Your time and effort are very much appreciated.

Happy Holiday