Command Button code to use with multi-column ListBox

[jammoca]

I have a ListBox ( called ... ListBox_1st_Class ) set up to display 2 columns of data found in .. PrintTemplate!V14:V44

I've set up the ListBox to allow multiple rows to be selected

I have a command button called ... CommandButton_Choose_These_Students ... which when pressed is supposed to send the information from the rows selected in the ListBox to be displayed in a table 2 columns wide starting at PrintTemplate!V49

The code I've entered into the Command Button is ...

Private Sub CommandButton_Choose_These_Students_Click()
Dim addme As Range
Dim x As Integer
Set addme = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then
        addme = Me.ListBox_1st_Class.List(x)
        addme.Offset(0, 1) = Me.ListBox_1st_Class.List(x, 1)
        addme.Offset(0, 2) = Me.ListBox_1st_Class.List(x, 2)
        Set addme = addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then Me.ListBox_1st_Class.Selected(x) = False
Next x
End Sub

However, I don't know vba, so didn't create it myself. I made this by amending some code I saw used on a youtube video, so can't figure what the line ... Set addme = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) ... is doing

Is that line instructing where to place the chosen data.

I need the 2-column data from the chosen rows in the listbox to be displayed in a table starting (top left cell) at ... PrintTemplate!V49

Can someone please help me amend this code to do that ?

Kind regards,

Chris

 

[MickG]

You say your Listbox has 2 columns , but you only quote column "V" as the column that fills it.
Your data that fills this listbox should be something like "V14:W44". (2 columns)
You also Quote the data to Results to start (Topleftcell) of PrintTemplate!V49, To me that is cell V49 !!!!
"Set Addme" is to tell you the next empty cell in column "V of sheet template.

Try this Code:-
Starting V49 this code should fill That column with the second column of your Listbox.

Private Sub CommandButton_Choose_These_Students_Click()
Dim Addme As Range
Dim x As Integer
If IsEmpty(Sheets("PrintTemplate").Range("V49")) Then
    Set Addme = Sheets("PrintTemplate").Range("V49")
Else
    Set Addme = Sheets("PrintTemplate").Range("V" & Rows.Count).End(xlUp).Offset(1, 0)
End If
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then
        'This is the first column That you Don't want !!!!!
        'addme = Me.ListBox_1st_Class.List(x)
        Addme.Value = Me.ListBox_1st_Class.List(x, 1)
        Set Addme = Addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then Me.ListBox_1st_Class.Selected(x) = False
Next x
End Sub

 

[jammoca]

Mick,

that worked brilliantly, but you were right ... I need the 1st column (from the students chosen in the list box) to fill from V49 down ... AND ... I need the 2nd column (from the students chosen in the list box) to fill from W49 down. Currently, because of my poor description, your code places the 2nd column data into v49 down

I wouldn't know how to amend your code to make that happen.

May I stretch the friendship and ask if you could tweak it for me ?

I really appreciate the work you've put into this, and for taking the time to help me.

Very kindest regards,

Chris

 

[jammoca]

Also, when I run your current code, the highlight that had appeared on the chosen names disappears, but I'd like the highlight to remain there ... how would I amend your code to do this ?

I'd like a separate command button that closes the UserForm, and when doing so, would THEN clear any highlight on the names previously chosen ... how would I code a command button to do those two things ?

Very kindest regards,

Chris

 

[MickG]

Try this:-

Private Sub CommandButton_Choose_These_Students_Click()
Dim Addme As Range
Dim x As Integer
If IsEmpty(Sheets("PrintTemplate").Range("V49")) Then
    Set Addme = Sheets("PrintTemplate").Range("V49")
Else
    Set Addme = Sheets("PrintTemplate").Range("V" & Rows.Count).End(xlUp).Offset(1, 0)
End If
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then
        Addme = Me.ListBox_1st_Class.List(x)
        Addme.Offset(, 1).Value = Me.ListBox_1st_Class.List(x, 1)
        Set Addme = Addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then Me.ListBox_1st_Class.Selected(x) = False
Next x
End Sub

 

[jammoca]

Mick, you're a genius, thankyou so much.

If I were to have four of those listboxes, called ...

ListBox_1st_Class
ListBox_2nd_Class
ListBox_3rd_Class
ListBox_4th_Class

How would I amend the code to ...

place the values from ListBox_1st_Class into V49 and W49 down ... just like the code currently does
place the values from ListBox_2nd_Class into Y49 and Z49 down
place the values from ListBox_3rd_Class into AB49 and AC49 down
place the values from ListBox_4th_Class into AE49 and AF49 down

Kind regards,

Chris

 

[MickG]

Just change the lines below as shown:-

[COLOR=#008000]'Change"V49"  below to "Y49" or "AB49" 0r "AE49[/COLOR]"
If IsEmpty(Sheets("PrintTemplate").Range("V49")) Then 
     [COLOR=#008000] 'Change "V49" Below  to "Y49" or "AB49" 0r "AE49"[/COLOR]
      Set Addme = Sheets("PrintTemplate").Range("V49")
Else
   [COLOR=#008000] 'Change  "V" below  to "Y" or "AB" 0r "AE"[/COLOR]
    Set Addme = Sheets("PrintTemplate").Range("V" & Rows.Count).End(xlUp).Offset(1, 0)
End If

 

[jammoca]

Hi Mick,

I'm confused. I don't know VBA so am unsure what you want me to do with your suggestions (in green).

I'm needing a code to go into a single Command Button (called CommandButton_Choose_These_Students), such that, after a user has finished selecting certain students from one or more of the four ListBoxes (ListBox_1st_Class and/or ListBox_2nd_Class and/or ListBox_3rd_Class and/or ListBox_4th_Class), upon clicking the CommandButton, any values chosen from ListBox_1st_Class will be sent to V49 and W49 down, any values chosen from ListBox_2nd_Class will be sent to Y49 and Z49 down, any values chosen from ListBox_3rd_Class will be sent to AB49 and AC49 down, and any values chosen from ListBox_4th_Class will be sent to AE49 and AF49 down.

Is this possible to code into a single commandbutton ?

Kind regards,

Chris

 

[MickG]

Below is the code for 2 Listboxes based on CommandButton1.
Try this code For your first 2 Listboxes.
When you have it working add two more bits of code as per the code below but changing the "Addme " addresses and the Listbox names accordingly, as previously shown.

I should add new codes one at a time in case you make some typos.

Private Sub CommandButton1_Click()
Dim Addme As Range
Dim x As Integer
If IsEmpty(Sheets("PrintTemplate").Range("V49")) Then
    Set Addme = Sheets("PrintTemplate").Range("V49")
Else
    Set Addme = Sheets("PrintTemplate").Range("V" & Rows.Count).End(xlUp).Offset(1, 0)
End If
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then
        Addme = Me.ListBox_1st_Class.List(x)
        Addme.Offset(, 1).Value = Me.ListBox_1st_Class.List(x, 1)
        Set Addme = Addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then Me.ListBox_1st_Class.Selected(x) = False
Next x

'###########
[COLOR=#008000]'Code2
' "Y49", Column "Y" &  "Me.ListBox_2nd_Class"[/COLOR]

If IsEmpty(Sheets("PrintTemplate").Range("Y49")) Then
    Set Addme = Sheets("PrintTemplate").Range("Y49")
Else
    Set Addme = Sheets("PrintTemplate").Range("Y" & Rows.Count).End(xlUp).Offset(1, 0)
End If
For x = 0 To Me.ListBox_2nd_Class.ListCount - 1
If Me.ListBox_2nd_Class.Selected(x) Then
        Addme = Me.ListBox_2nd_Class.List(x)
        Addme.Offset(, 1).Value = Me.ListBox_2nd_Class.List(x, 1)
        Set Addme = Addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_2nd_Class.ListCount - 1
If Me.ListBox_2nd_Class.Selected(x) Then Me.ListBox_2nd_Class.Selected(x) = False
Next x
End Sub

 

[jammoca]

Mick, thankyou again so very much.

I crept up on it, like you suggested, by adding just one ListBox at a time, and it worked brilliantly.

So far, the code allows a user to click on individual students from any of the four ListBoxes (each with 2 columns of data for each student ... Name and ID Number) and that data is sent to columns V and W, or Y and Z, or AB and AC, or AE and AF, however, I'd like to add four more ListBoxes called ... ListBox_1st_Class_Print_All, ListBox_2nd_Class_Print_All, ListBox_3rd_Class_Print_All, ListBox_4th_Class_Print_All which, when clicked, should send the entire class list of student names (and their ID Numbers) to those same Column sets as before ... V and W, etc etc.

Whereas the original four ListBoxes each have the capacity for up to 31 Students to be clicked, and are set up with 2 columns, these newest four ListBoxes only have capacity for only one thing to be clicked ... 'Print All' .... and are set up with only one column

For example ... If a user clicks the only option available in ListBox_1st_Class_Print_All, I would like the entire Student List, and their ID numbers, to the same Column sets as before ... V and W

In other words ....

ListBox_1st_Class_Print_All_, if clicked, needs to send all the student names (and Id Numbers) from the range ... PrintTemplate!V14:W44 ... to V49 and W49 down
ListBox_2nd_Class_Print_All_, if clicked, needs to send all the student names (and Id Numbers) from the range ... PrintTemplate!Y14:Z44 ... to Y49 and Z49 down
ListBox_3rd_Class_Print_All_, if clicked, needs to send all the student names (and Id Numbers) from the range ... PrintTemplate!AB14:AC44 ... to AB49 and AC49 down
ListBox_4th_Class_Print_All_, if clicked, needs to send all the student names (and Id Numbers) from the range ... PrintTemplate!AE14:AF44 ... to AE49 and AF down

I don't know if this is important, but not every cell in those sheet ranges will have a student name and ID in them ... eg: V14:W44 has 31 rows, but the class might only have 23 students, so only 23 rows would have data.

Mick, I apologise profusely for taking up so much of your time. If you could figure how to amend the current code (which I'll include below) to include these four new ListBoxes, I would 'bear your child' as they say here in Australia.

Very kindest regards,

Chris

Private Sub CommandButton_Choose_These_Students_Click()
Dim Addme As Range
Dim x As Integer
If IsEmpty(Sheets("PrintTemplate").Range("V49")) Then
    Set Addme = Sheets("PrintTemplate").Range("V49")
Else
    Set Addme = Sheets("PrintTemplate").Range("V" & Rows.Count).End(xlUp).Offset(1, 0)
End If
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then
        Addme = Me.ListBox_1st_Class.List(x)
        Addme.Offset(, 1).Value = Me.ListBox_1st_Class.List(x, 1)
        Set Addme = Addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_1st_Class.ListCount - 1
If Me.ListBox_1st_Class.Selected(x) Then Me.ListBox_1st_Class.Selected(x) = False
Next x


'###########
'Code2
' "Y49", Column "Y" &  "Me.ListBox_2nd_Class"


If IsEmpty(Sheets("PrintTemplate").Range("Y49")) Then
    Set Addme = Sheets("PrintTemplate").Range("Y49")
Else
    Set Addme = Sheets("PrintTemplate").Range("Y" & Rows.Count).End(xlUp).Offset(1, 0)
End If
For x = 0 To Me.ListBox_2nd_Class.ListCount - 1
If Me.ListBox_2nd_Class.Selected(x) Then
        Addme = Me.ListBox_2nd_Class.List(x)
        Addme.Offset(, 1).Value = Me.ListBox_2nd_Class.List(x, 1)
        Set Addme = Addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_2nd_Class.ListCount - 1
If Me.ListBox_2nd_Class.Selected(x) Then Me.ListBox_2nd_Class.Selected(x) = False
Next x




'###########
'Code3
' "AB49", Column "AB" &  "Me.ListBox_3rd_Class"


If IsEmpty(Sheets("PrintTemplate").Range("AB49")) Then
    Set Addme = Sheets("PrintTemplate").Range("AB49")
Else
    Set Addme = Sheets("PrintTemplate").Range("AB" & Rows.Count).End(xlUp).Offset(1, 0)
End If
For x = 0 To Me.ListBox_3rd_Class.ListCount - 1
If Me.ListBox_3rd_Class.Selected(x) Then
        Addme = Me.ListBox_3rd_Class.List(x)
        Addme.Offset(, 1).Value = Me.ListBox_3rd_Class.List(x, 1)
        Set Addme = Addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_3rd_Class.ListCount - 1
If Me.ListBox_3rd_Class.Selected(x) Then Me.ListBox_3rd_Class.Selected(x) = False
Next x






'###########
'Code4
' "AE49", Column "AE" &  "Me.ListBox_4th_Class"


If IsEmpty(Sheets("PrintTemplate").Range("AE49")) Then
    Set Addme = Sheets("PrintTemplate").Range("AE49")
Else
    Set Addme = Sheets("PrintTemplate").Range("AE" & Rows.Count).End(xlUp).Offset(1, 0)
End If
For x = 0 To Me.ListBox_4th_Class.ListCount - 1
If Me.ListBox_4th_Class.Selected(x) Then
        Addme = Me.ListBox_4th_Class.List(x)
        Addme.Offset(, 1).Value = Me.ListBox_4th_Class.List(x, 1)
        Set Addme = Addme.Offset(1, 0)
        End If
    Next x
For x = 0 To Me.ListBox_4th_Class.ListCount - 1
If Me.ListBox_4th_Class.Selected(x) Then Me.ListBox_4th_Class.Selected(x) = False
Next x




End Sub