Transfer Multi-Select ListBox To Range Of Cells

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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

See Also: Multi-Selected ListBox & Loop through all Controls on a UserForm & Move ListBox Item Up/Down

The VBA code below will transfer all selected rows and columns of a Multi-Selected ListBox. On loading the UserForm, 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
    Next
     
    '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
            Next
        End With
         Unload Me
    Else ' NO listbox row chosen
         MsgBox "Nothing chosen", vbCritical
    End If

End Sub

Download working example

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates