A control displays list of values in VBA User Form. The list can be populated with the help of loop or AddItem method. In this article we will see how to populate Listbox from a Range directly in one line code and How to read all items from a Listbox and populate a range.
Create a user form as shown below and put one list box name it “lstProducts” and two buttons as “cmdFill” and “cmdRange” and label as “Fill List” and “Write to Range” respectively. And write code on each button’s event as shown below:
Fill List Code example
Private Sub cmdFill_Click() 'Declare range object Dim oRange As Range 'Bind selection Set oRange = Selection 'Populate list from range lstProducts.RowSource = oRange.Address 'memory cleanup Set oRange = Nothing End Sub
Write to Range code example
Private Sub cmdRange_Click() 'Declare worksheet object Dim oWkSheet As Worksheet 'Bind active sheet object Set oWkSheet = ActiveSheet 'Get total available rows in list box Dim totalRows As Integer totalRows = lstProducts.ListCount - 1 'Declare start row range where data needs to be written Dim startRow As Integer startRow = 10 'Populate all items in the range With oWkSheet .Range("A" & startRow & ":A" & startRow + totalRows) = lstProducts.List End With 'Memory cleanup Set oWkSheet = Nothing End Sub
Select a range and press F5 to run the program and Click on Fill List button and list would be populated as shown below:
Now click on Write to Range button and notice the result in Range A10 onward the range is populated with all the items listed in the listbox.