Transfer Multi-Select ListBox To Range Of Cells

< Back to Search results

 Category: [Excel]  Demo Available 

Transfer Multi-Select ListBox To Range Of Cells


Transfer Selected Rows Of Multi-Select & Multi-Column ListBox To Range Of Cells On Worksheet


The VBA code below will transfer all selected rows and columns of a multi-selected listbox. On loading the user form, the ColumnCount Property of the ListBox is set to as many columns as the RowSource Property.

Download working example

Private Sub UserForm_Initialize()
    'Set ListBox ColumnCount to the same as RowSource
    ListBox1.ColumnCount = Range(ListBox1.RowSource).Columns.Count
End Sub

Private Sub TransferButton_Click()
 Dim lItem As Long, lRows As Long, lCols As Long
 Dim bSelected As Boolean
 Dim lColLoop As Long, lTransferRow As Long
 'Pass row & column count to variables
 'Less 1 as "Count" starts at zero
 lRows = ListBox1.ListCount - 1
 lCols = ListBox1.ColumnCount - 1
    'Ensure they have at least 1 row selected
    For lItem = 0 To lRows
        'At least 1 row selected
        If ListBox1.Selected(lItem) = True Then
            'Boolean flag
            bSelected = True
            'Exit for loop
            Exit For
        End If
    'At least 1 row selected
    If bSelected = True Then
        With Sheet1.Range("D1", Sheet1.Cells(lRows + 1, 4 + lCols)) 'Transfer to range
            .Cells.Clear 'Clear transfer range
            For lItem = 0 To lRows
                If ListBox1.Selected(lItem) = True Then 'Row selected
                  'Increment variable for row transfer range
                  lTransferRow = lTransferRow + 1
                    'Loop through columns of selected row
                    For lColLoop = 0 To lCols
                       'Transfer selected row to relevant row of transfer range
                       .Cells(lTransferRow, lColLoop + 1) = ListBox1.List(lItem, lColLoop)
                        'Uncheck selected row
                        ListBox1.Selected(lItem) = False
                    Next lColLoop
                End If
        End With
         Unload Me
    Else ' NO listbox row chosen
         MsgBox "Nothing chosen", vbCritical
    End If

End Sub

Download working example

See also:

Index to Excel VBA Code
Sum/Count Cells By Fill Or Background Color in Excel
Sum Excel Ranges Diagonally
Excel Function That Sums Every Nth Cell In a Specified Range
Sum Values In Excel Meeting Up To 5 Criteria/Conditions
Excel Custom Function: Sum Top/Bottom X Numbers In 1 Column or Row


See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.




stars (0 Reviews)