Command Button code to use with multi-column ListBox

J

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
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 ...

Code: 
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
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.

Code: 
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Try this:-
Code: 
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
 
Upvote 0
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
 
Upvote 0
Just change the lines below as shown:-
Code: 
[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
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
Code: 
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
 
Last edited:
Upvote 0
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

Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
359
Fuji
F
T
  • Solved
Run Time 13 Mismatch Error
Replies
8
Views
653
Alex Blakenburg
A
M
  • Question Question
Re-produce code to copy last row from listbox on userform to sheet
Replies
3
Views
167
MKLAQ
M
V
  • Solved
VBA search in listbox when used combobox and textbox-To be modified
2
Replies
13
Views
1K
velu888
V
C
  • Question Question
Excel Userform Listbox Search issues
Replies
3
Views
758
Akuini
Akuini

Forum statistics

Threads
1,224,829
Messages
6,181,218
Members
453,024
Latest member
Wingit77

Share this page

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