Does anyone know if you can move items in a multiselect list box each containing 5 columns to another list box? I have been successful moving one-column of my data between listboxes and removing with cmdbuttons but not multiple columns. What I am trying to do is run an advanced filter to return a list of students in ListBox1, and a list of training modules in Listbox2 with a single cmdbutton, but that is as far as I get. Listbox1 is a multiselect list box, ListBox2 is a single select list box. The completion date is a a text box (Trng9)
My problem is:
1. I need to be able to select multiple students in ListBox1 containing 5 columns, move all the selected students information to Listbox3.
2. I need to select one training module from my search results in ListBox2, which also contains 5 columns of data, and assign that class information to all the students selected and moved to ListBox3.
3. Then I need to also add a completion date via a txtBox (Trng9) to all students in ListBox3. My result is a list of students that completed a module of training on a certain date.
4. Finally, I need to add, through the use of a command button, the entire list of students in ListBox3, their training and completion date to the next available row in a worksheet (Data) which is a historic list of all completed training.
Can this type of move from two listboxes to a third consisting of 11 total columns be done?
I have looked at so many blogs and videos to try to find this and just don't see anything. If anyone can help or point me to a source I would be grateful.
My problem is:
1. I need to be able to select multiple students in ListBox1 containing 5 columns, move all the selected students information to Listbox3.
2. I need to select one training module from my search results in ListBox2, which also contains 5 columns of data, and assign that class information to all the students selected and moved to ListBox3.
3. Then I need to also add a completion date via a txtBox (Trng9) to all students in ListBox3. My result is a list of students that completed a module of training on a certain date.
4. Finally, I need to add, through the use of a command button, the entire list of students in ListBox3, their training and completion date to the next available row in a worksheet (Data) which is a historic list of all completed training.
Can this type of move from two listboxes to a third consisting of 11 total columns be done?
I have looked at so many blogs and videos to try to find this and just don't see anything. If anyone can help or point me to a source I would be grateful.
VBA Code:
Private Sub cmdMoveRight_Click()
Dim iCtr As Long
For iCtr = 0 To Me.Listbox1.ListCount - 1
If Me.Listbox1.Selected(iCtr) = True Then
Me.Listbox2.AddItem Me.Listbox1.List(iCtr)
' For iCtr = Me.Listbox1.ListCount - 1 To 0 Step -1
' If Me.Listbox1.Selected(iCtr) = True Then
' toListBox2.AddItem Me.Listbox1.List(i, 0)
' toListBox1.List(toListBox1.ListCount - 1, 1) = toListBox2.List(i, 1)
End If
Next iCtr
For iCtr = Me.Listbox1.ListCount - 1 To 0 Step -1
If Me.Listbox1.Selected(iCtr) = True Then
'Me.Listbox1.RemoveItem iCtr
End If
Next iCtr
End Sub
Private Sub cmdMoveLeft_Click()
Dim iCtr As Long
For iCtr = 0 To Me.Listbox2.ListCount - 1
If Me.Listbox2.Selected(iCtr) = True Then
'Me.Listbox1.AddItem Me.Listbox2.List(iCtr)
End If
Next iCtr
For iCtr = Me.Listbox2.ListCount - 1 To 0 Step -1
If Me.Listbox2.Selected(iCtr) = True Then
Me.Listbox2.RemoveItem iCtr
End If
Next iCtr
End Sub
Private Sub cmdLoadTrng_Click()
Dim addme As Range
Dim x As Integer
Set addme = Sheet2.Cells(Rows.Count, 32).End(xlUp).Offset(1, 0)
For x = 0 To Me.Listbox1.ListCount - 1
If Me.Listbox1.Selected(x) Then
addme = Me.Listbox1.List(x)
addme.Offset(0, 1) = Me.Listbox1.List(x, 1)
addme.Offset(0, 2) = Me.Listbox1.List(x, 2)
addme.Offset(0, 3) = Me.Listbox1.List(x, 3)
addme.Offset(0, 4) = Me.Listbox1.List(x, 4)
Set addme = addme.Offset(1, 0)
End If
Next x
For x = 0 To Me.Listbox1.ListCount - 1
If MeListbox1.Selected(x) Then Me.Listbox1.Selected(x) = False
Next x
End Sub