I have a UserForm in which I have a List Box. The user can add items
to the list box. At the end I have to get all items from the list box
control and transfer them into a spreadsheet column.
Appreciate any help to achieve this.

Thanks,
Anand.

Sub ListBoxToCells()

Dim indexTo As Long
Dim i As Long
Dim j As Long
Dim var As Variant
'Get how many records exist within the listbox and store that into a
variable
indexTo = ListBox1.ListCount
'This will hold the List so we can go through it
var = ListBox1.List
j = 1
'The List starts at element 0 (in the array), so we'll start the i
counter there.
For i = 0 To indexTo - 1
'Print out to the Cells.
Sheet1.Cells(j, 1).Value = var(i, 0)
j = j + 1
Next i
End Sub

Hope this helps, if you wish for me to go into more detail then I shall, but
it's all fairly straight forward stuff.

"Anand" wrote:

I have a UserForm in which I have a List Box. The user can add items
to the list box. At the end I have to get all items from the list box
control and transfer them into a spreadsheet column.
Appreciate any help to achieve this.

Thanks,
Anand.

Sub ListBoxToCells()

Dim indexTo As Long
Dim i As Long
Dim j As Long
Dim var As Variant
indexTo = ListBox1.ListCount
var = ListBox1.List
j = 1
For i = 0 To indexTo - 1
Sheet1.Cells(j, 1).Value = var(i, 0)
j = j + 1
Next i
End Sub

Heres the code without the comments as they don't stay on the right lines
when I copy and paste it into here, so I wouldn't want it not to work because
something that should be commented, isn't.



"NateBuckley" wrote:

Sub ListBoxToCells()

Dim indexTo As Long
Dim i As Long
Dim j As Long
Dim var As Variant
'Get how many records exist within the listbox and store that into a
variable
indexTo = ListBox1.ListCount
'This will hold the List so we can go through it
var = ListBox1.List
j = 1
'The List starts at element 0 (in the array), so we'll start the i
counter there.
For i = 0 To indexTo - 1
'Print out to the Cells.
Sheet1.Cells(j, 1).Value = var(i, 0)
j = j + 1
Next i
End Sub

Hope this helps, if you wish for me to go into more detail then I shall, but
it's all fairly straight forward stuff.

"Anand" wrote:

I have a UserForm in which I have a List Box. The user can add items
to the list box. At the end I have to get all items from the list box
control and transfer them into a spreadsheet column.
Appreciate any help to achieve this.

Thanks,
Anand.