CODEWITHSUNDEEP
excelvbalistbox
Meru
Meru

Reputation: 11

Copy values from a ListBox to worksheet cells

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?

Upvotes: 0

Views: 12088

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

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.

Upvotes: 0

Related Questions