 |
|
|
|
|
|
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 special@ozgrid.com 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
GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS