Moving Data from ListBox to Excel Spreadsheet

I am currently teaching myself VBA and how to create my own userforms.

I have been following these handy tutorials here (link: http://www.excel-easy.com/vba/…-list-box-selections.html).

This tutorial walks you through how to create a userform which allows a user to select some items in List Box 1 and then add/ remove them from List Box 2. I want to add some additional functionality to this userform by allowing a user to 'print' or 'transfer' their selections in List Box 2 to a table in a sheet.

I have tried to 'transfer' the data into the first empty row on Sheet1 by using this code (ref link: http://www.excel-easy.com/vba/userform.html)

Private Sub OKButton_Click()


Dim emptyRow As Long


'Make Sheet1 active
Sheet1.Activate


'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer information
Cells(emptyRow, 1).Value = ListBox2.Value

But a) it does not work when I click 'OK' and b) I suspect it's missing something in order to transfer all the selections in List Box 2

Any help on this topic would be much appreciated.

Hi Rab,

Please find attached the spreadsheet with my code. Hopefully this will help explain what I'm attempting to do.

Basically, I have some raw data about different products which are sold in different quantities throughout the year across various stores.

The userform allows a user to choose multiple stores and products.

The stores and products they selected then get 'transfered' to a table.

This table is then used as the criteria for an advanced filter sort. I will add in a macro later so that way this is done automatically.

Thanks, rabsofty! My userform is now working like I expected it to.

I was wondering how I could improve this code so that instead of transferring the data from my listbox into an empty row on Sheet 1, I could specify a specific cell where it should be pasted into.

-> Currently: Data from listbox is pasted into the first empty row in Sheet 1
-> Goal: Specify where the data should be pasted into in Sheet 1

I have done this by slightly altering rabsofty's code above:

'Replace # with appropriate number
Range(Cells(ROW #, COLUMN #), Cells(ROW # + ListBox2.ListCount -1, COLUMN #)).Value = ListBox2.List

Is there a neater way to do this?