copy and paste values from a listbox to cells

[Guest]

I would like to save the values contained in a list box to a cell range for later use.

 

[Doug Glancy]

Doug,

You don't say what kind of listbox and there would be lots of ways to do it,
but this one works with a listbox from the controls toolbar. It starts at
A2 and copies down. I assumed only one column in your listbox:

Sub test()
Dim i As Integer
Dim row_num As Integer, col_num As Integer

row_num = 2
col_num = 1

With Sheet1.ListBox1
For i = 1 To .ListCount
Sheet1.Cells(row_num, col_num) = .List(i - 1)
row_num = row_num + 1
Next i
End With
End Sub

hth,

Doug

I would like to save the values contained in a list box to a cell range

for later use.

 

[onedaywhen]

Why the loop?

Sub test()
Dim a As Variant
a=Sheet1.ListBox1.List
Sheet1.Range("A2").Resize(Ubound(a,1) - LBound(a,1) + 1).Value = a
End Sub

--

 

[Doug Glancy]

Hey onedaywhen,

Because I didn't know how to do it this way. As often happens, I suspected
something like what you've shown is possible, but couldn't figure out how.
(I have a lot to learn about arrays). I'll remember it now though.

I should have a standard disclaimer, "I'm still learning and though I'm
trying to be helpful, this may not be the best solution. If not, a better
one will follow."

Thanks for increasing my knowledge,

Doug

 

[Guest]

Thank you both for the enlightenment. Although I can understand the approach provided by Doug Glancy, the array approach is interesting.