How to get an output from a list box to populate some cells

Question

I've created a userform with a couple of list boxes in. Listbox1 has all items in it, the user can select multiple values to then move into Listbox2. once they are in listbox2 I need them to populate some cells. With 1 list item per cell.

I'm having a pain trying to work out how to do it. So far all I've got is:

Private Sub CommandButton1_Click()
Dim tmpMsg As String
Dim t As Long

 tmpMsg = "Selected categories:"

For t = 1 To ListBox2.ListCount
 tmpMsg = tmpMsg & vbNewLine & ListBox2.List(t - 1)
Next

 Worksheets("Specialist Prices").Activate
    Range("a1").Select
    ActiveCell.Value = tmpMsg

End Sub

This populates cell A1 with the entire set of list items. but I don't know how to put 1 value in a1 then move down and put the next in A2 and so on until all items are accounted for.

Any help would be appreciated.

Alex de Jong Alex de Jong · Accepted Answer · 2019-02-27T10:46:47

Right now you are creating one string. It has linebreaks in it, but it´s still one text. If you are not using the tmpmsg for something else, you could use the for-loop to populate your sheet

For t = 1 To ListBox2.ListCount
  Thisworkbook.sheets("Specialist Prices").Range("A" & t).value = ListBox2.List(t - 1)
Next

You can even do it faster by adding an array to the entire range. ListBox2.List is an array of values and you can paste that array into a range of cells:

ThisWorkbook.sheets("Specialist Prices").Cells(1,1).Resize(ListBox2.ListCount,1) = ListBox2.List

The Resize method here makes sure that the range contains the same amount of cells as there are elements in ListBox2. It resizes the range from 1 cell to a range of ListBox2.ListCount amount of rows and 1 column.

How to get an output from a list box to populate some cells

1 Answers