Range to Listbox Vs. Listbox to Range User Form Control VBA

ListBox

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.

UserForm

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

Execution

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.

Next >> Register or Install a DLL at run time using VBA code

Share on Facebook

Tweet