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 might like to consider the code below, By adding this code to each listbox you can select all the values by just double clicking the listbox, then you could use the original code to place then in your sheet.
NB:- If you double click the Listbox a second time, all the items will become unselected.
NB:-Change listbox name as required.

Private Sub ListBox_1st_Class_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim x As Long
Static Fd As Boolean
Fd = Fd Xor True
With Me.ListBox_1st_Class
    For x = 0 To .ListCount - 1
        .Selected(x) = Fd
    Next x
End With
End Sub

 

[jammoca]

Once again, Mick, you've solved my problem. That worked an absolute treat.

I can't thank you enough.

Very, very kindest regards,

Chris

 

[MickG]

You're very welcome
Regrds Mick

 

[jammoca]

Hello again Mick,

I hate to do this, and I'm not sure if it's breaking a Forum rule, but I was wondering if you could have a look at another query I posted earlier today, but has had no replies at this stage.

The topic heading was ... Code to create temporary Sheets, Print them, the Delete the Sheets ..

I don't know how to link directly to the post, I'm sorry for that.


Kindest regards,

Chris (Aussie Chris)