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.
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
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 [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
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