Delete multi select rows from listbox

Rfriend

Board Regular
Joined
May 10, 2021
Messages
73
Office Version
  1. 2010
Platform
  1. Windows
Hello,


I currently have 3 listboxes. Listbox 1 & 2 pull data from separate locations. Clicking on selections in each listbox and using a command button to combine, the selected lines of data are combined into listbox 3. For example, selecting multiple students in listbox1, a training module in listbox 2, are combined into listbox3. This will show all students selected assigned a single training module. Using a command button the combined data is sent to a worksheet.

I need a formula that will allow me to remove a single or multiple lines of data from listbox3 using a command button before adding the remaining data to the database records.

The code to combine the data is below. Any help is appreciated.

VBA Code:
Private Sub cmdMove_Click()
' Combine employee, training, and completion date to listbox3
    Dim table1 As ListObject, table2 As ListObject, table3 As ListObject
    Dim ListBox1SelectedRows As Collection
    Dim ListBox2SelectedRow As Long
    Dim r As Long
    Dim table1Row As Range
    Dim table2Row As Range
    Dim table3Row As ListRow
        Set table1 = Worksheets("Sheet2").ListObjects("Table1")
        Set table2 = Worksheets("Sheet2").ListObjects("Table2")
        Set table3 = Worksheets("Sheet2").ListObjects("Table3")
        Set ListBox1SelectedRows = New Collection
            Application.ScreenUpdating = False
    
    If Me.Trng9 = "" And Me.Trng10 <> "" Then
        MsgBox "Missing Date mm/dd/yyyy"
            Exit Sub
    End If
    
    If Me.Trng10 = "" And Me.Trng9 <> "" Then
        MsgBox "Missing Training Hours"
            Exit Sub
    End If
    
    If Me.Trng10 = "" And Me.Trng9 = "" Then
        MsgBox "Missing Date & Hours of Training"
            Exit Sub
    End If
    
    With Me.Listbox1
        For r = 0 To Me.Listbox1.ListCount - 1
            If .Selected(r) Then ListBox1SelectedRows.Add r + 1
        Next
    End With
    
    With Me.Listbox2
        ListBox2SelectedRow = 0
            For r = 0 To .ListCount - 1
                If .Selected(r) Then ListBox2SelectedRow = r + 1
            Next
    End With
    
    If ListBox1SelectedRows.Count = 0 Or ListBox2SelectedRow = 0 Then
        MsgBox "You must select at least 1 student and 'ONLY' 1 training subject"
        Exit Sub
    End If
    
    For r = 1 To ListBox1SelectedRows.Count
        Me.Listbox3.RowSource = ""
            If table3.DataBodyRange Is Nothing Then
                Set table3Row = table3.ListRows.Add(1)
            Else
                Set table3Row = table3.ListRows.Add
            End If
        Me.Listbox3.RowSource = "Table3"
            Set table1Row = table1.ListRows(ListBox1SelectedRows(r)).Range
                table1Row.Copy table3Row.Range
            Set table2Row = table2.ListRows(ListBox2SelectedRow).Range
                table2Row.Copy table3Row.Range(, table3.ListColumns("Category").Index)
                table3Row.Range(, table3.ListColumns("Completed").Index).Value = Format(Me.Trng9, "mm/dd/yyyy")
                table3Row.Range(, table3.ListColumns("Hours").Index).Value = Format(Me.Trng10, "#.0")
    Next
            With Listbox3
            .TopIndex = .ListCount - 1
        End With    
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top