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:…-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:

    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.

  • jodi12, in your code for the OKButton_Click()
    change the line shown with the replace with code.

    'Transfer Info
    'your old code - Cells(eRow, 1).Value = ListBox2.Value
    'replace with
    Range(Cells(eRow, 1), Cells(eRow + ListBox2.ListCount - 1, 1)).Value = ListBox2.List
    End Sub
  • 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?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!