Excel VBA Listbox Selection Types And Copying The Selected Data

Copy /Transfer Selected Rows Of Multiselect And Multi Column Listbox To Range Of Cells On Sheet 



Excel VBA listbox selection types :
Single Item Selecting
     VBA code to select single item :        ListBox2.MultiSelect = 0  


 Multiple Items Selecting
      VBA code to select multiple item :    ListBox2.MultiSelect = 1  
➤ Multiple Selection By Pressing  keys
      VBA code to select multiple item by pressing Ctrl and Shift keys :   ListBox2.MultiSelect = 2  

   
In our template, we used option buttons to determine the listbox item selection type. Codes of listbox selection types in the template :
Private Sub OptionButton1_Click()
ListBox2.MultiSelect = 0
End Sub

Private Sub OptionButton2_Click()
ListBox2.MultiSelect = 1
End Sub

Private Sub OptionButton3_Click()
ListBox2.MultiSelect = 2
End Sub

Listbox controls are set to single item selection by default. Using the above VBA codes, multiple selections can be made between listbox items.
Also , in Properties section of VBA Editor , listbox selection type can be changed by selecting MultiSelect from the listbox properties when listbox (the name of the listbox in our template is ListBox2) is selected from the drop-down list.



        With the checkbox control at the top, all of the lisbox items can be selected, but for this the listbox must be set according to multiple selections (option button 2 or option button 3).
The codes of this checkbox to select all listbox items :
Private Sub CheckBox1_Click()
Dim r As Long
If CheckBox1.Value = True Then
    For r = 0 To ListBox2.ListCount - 1
        ListBox2.Selected(r) = True
    Next r
   Else
      For r = 0 To ListBox2.ListCount - 1
        ListBox2.Selected(r) = False
    Next r
    End If
End Sub


The selected listbox items by pressing the Copy button are copied to the other sheet (SeletctedData). Codes of Copy button :
Dim Litem As Long, LbRows As Long, LbCols As Long
 Dim bu As Boolean
 Dim Lbloop As Long, Lbcopy As Long

 LbRows = ListBox2.ListCount - 1
 LbCols = ListBox2.ColumnCount - 1
 
    For Litem = 0 To LbRows
    If ListBox2.Selected(Litem) = True Then
          bu = True
          Exit For
    End If
    Next

    If bu = True Then
    With Sheets("SelectedData").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
   
            For Litem = 0 To LbRows
                If ListBox2.Selected(Litem) = True Then 'Row selected
                  'Increment variable for row transfer range
                  Lbcopy = Lbcopy + 1
            For Lbloop = 0 To LbCols
                       'Transfer selected row to relevant row of transfer range
            .Cells(Lbcopy, Lbloop + 1) = ListBox2.List(Litem, Lbloop)
                     
           Next Lbloop
                End If
            Next
            For m = 0 To LbCols
                With Sheets("SelectedData").Cells(Rows.Count, 1).End(xlUp).Offset(0, m).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = 23
        End With
Next
        End With
     
    Else
         MsgBox "Nothing chosen", vbCritical
    End If
MsgBox "The Selected Data Are Copied.", vbInformation
Sheets("SelectedData").Select


The first blank cell in column A of SelectedData sheet is selected while transferring . A border is drawn under of transfered data.



12 comments:

  1. Very nice! Danke

    ReplyDelete
  2. Excellent work. Please tell me How to down load with vba code.

    ReplyDelete
  3. I copy pasted the "send to" code from the commandbutton 1 into my uderform code and replaced the listbox to my listbox (name) and replaced the sheet name where I want it to be sent at, however, it keeps giving me an out of range message

    ReplyDelete
  4. ls sheet name correct? Sheets("SelectedData")

    ReplyDelete
  5. CAN YOU PLEASE TELL HOW TO USE ?

    ReplyDelete
  6. Yes, I changed all the "SelectedData" to my sheet name, still getting the error though :/

    ReplyDelete
  7. Nevermind, I created a new sheet blank sheet and it worked!! thank you!

    ReplyDelete
  8. This is awesome! So helpful!

    ReplyDelete
  9. where's the code? :D

    ReplyDelete
    Replies
    1. Codes are in the workbook. Download it and press Alt+F11 keys on sheet. So, you can view the codes.

      Delete
  10. Hi The DOWNLOAD Link is not working. please place file in another site. so we can easily download.

    ReplyDelete
    Replies
    1. The above link is not allow to INDIA customers..
      Ur help is really appreciated....

      Delete