Copy values from a ListBox to worksheet cells

Question

I'm working with VBA and have a problem I just can't find a solution for. I created a UserForm with a TextBox, a ListBox, and two Buttons. If you enter a text to the TextBox and click the Button1, then the text of the TextBox will be added via the command ListBox.AddItem to the Listbox. This part works perfectly fine.

If you click on Button2, it shall copy the entries of the ListBox to the Excel Sheet. I'm having a problem with this part. The code is as follows:

For i = 0 To ListBox1.ListCount - 1
    ListBox1.Selected(i) = True
    ActiveSheet.Cells(2, 3) = ListBox1.Value
    ListBox1.Selected(i) = False
Next i

This code works perfectly fine if I open the UserForm and click once just anywhere in the ListBox. From then the code works without problems. I also can close the UserForm and open it again. It will still work.

The problem appears if I open the whole Excel File and the UserForm and don't click once somewhere in the ListBox, it just doesn't copy the content. The code itself still works and is performed. It just seems as the command ListBox1.Value has no content. Is there a better way to solve my issue?

Your code writes everything to the same cell, is that what you want? Moreover, instead of selecting the item, using ListBox1.Value and then deselecting it, you can use ListBox1.List(i). — 41686d6564
I reduced the code to it's essential parts. Usually I search also for the right cell, but the loop would be just distracting for the problem itself. So it's writting everything to one cell for now. So by using ListBox1.List(i) it automatically select the item and copies it to the cell? I'll try it. — Meru
When you remove parts of your code, you should make sure that the new code produces the same problem. Read How to create a Minimal, Complete, and Verifiable example for more. That being said, the code in your question should work fine although it's not the right way to do what you're trying to do. Anyway, try with ListBox1.List(i) as I suggested and let us know if you still have the same problem. Make sure to try it with the shortened version of your code first — 41686d6564

Subodh Tiwari sktneer Subodh Tiwari sktneer · Accepted Answer · 2018-08-29T17:39:04

If you want to place the items from the already populated ListBox onto the Sheet, you may try something like this...

Dim x
x = Me.ListBox1.List
Range("C2").Resize(UBound(x) + 1, 1).Value = x

Tweak it as per your requirement.

Copy values from a ListBox to worksheet cells

1 Answers