Selecting multi Rows and Colums in a List Box

[pcarmour]

Hi, I have an Excel VBA user form called 'Booking' and a list box called 'Trades' The list box has 15 columns and about 100 rows of data in various row size groups. I have set the list box ListStyle to Plain and MultiSelect to Extended as I want to be able to select multiple rows using shift and click rather than using option buttons. My current code is:
With Booking.Trades
Range("A53").Value = .List(.ListIndex, 0)
Range("B53").Value = .List(.ListIndex, 1)
Range("C53").Value = .List(.ListIndex, 2)
Range("D53").Value = .List(.ListIndex, 3)
Range("E53").Value = .List(.ListIndex, 4)
Range("F53").Value = .List(.ListIndex, 5)
Range("G53").Value = .List(.ListIndex, 6)
Range("H53").Value = .List(.ListIndex, 7)
Range("I53").Value = .List(.ListIndex, 8)
'Range("J53").Value = .List(.ListIndex, 9)
Range("K53").Value = .List(.ListIndex, 10)
Range("L53").Value = .List(.ListIndex, 11)
Range("M53").Value = .List(.ListIndex, 12)
Range("N53").Value = .List(.ListIndex, 13)
Range("O53").Value = .List(.ListIndex, 14)

Which just adds the first row of selected data to my spread sheet in row 53


What I am trying to achieve is to add all selected rows of data to rows 53 down depending upon the total number of rows selected.
Any help would again be very much appreciated.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

Regards,
Peter.

[snb]

[VBA]
Private Sub CommandButton1_Click()
i = 0
For j = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(j) Then
Cells(53, 1).Offset(i).Resize(, UBound(ListBox1.List, 2) + 1) = Application.Index(ListBox1.List, j + 1)
i = i + 1
End If
Next
End Sub
[/VBA]

[pcarmour]

Sorry, trying your code again...
Ok That's working but after loading, the macro halts/stops on line Cells(53,1)...................
As I say the data ha been transferred as required but not sure why it halts.
Brilliant coding, thank you
Regards, Peter

[snb]

Did you try the last version in #2 ?

[pcarmour]

Yes, your version 2 tried

[snb]

Did you select more than 1 item in the listbox ?
I can't check your workbook from a distance...

[pcarmour]

Yes In this instance I selected 5 rows.
I understand you are working blind but you seem to understand me - thank you

Ok I have run again and all is working when I select rows lower down, it seems to only halt when I select the top group of rows

[pcarmour]

Hi, I have now tried various runs and have identified the fact that the code only stops if one of the rows doesn't have data in every column. I think I can work around that so Thank you again for your expertise, I'll let you know how it progresses, good night, Thanks Regards, Peter.

[snb]

I can't replicate your findings.
Cfr. the attachment.

[pcarmour]

Hi, I'm Very pleased to say that your code is working Brilliantly. I now think the problem may have been some characters such as ((((<> that were in one of the columns, but all working now, thank you.
Regards, Peter.

[pcarmour]

Hi, One further question, how can I cut the selection in the List Box rather than copy?
Peter.

[snb]

[VBA]Private Sub knop_verwijder_Click()
i = 0
For j = UBound(ListBox1.List) To 0 Step -1
If ListBox1.Selected(j) Then
Sheets(1).Cells(20, 1).Offset(i).Resize(, UBound(ListBox1.List, 2) + 1) = Application.Index(ListBox1.List, j + 1)
ListBox1.RemoveItem j
i = i + 1
End If
Next
End Sub[/VBA]

[pcarmour]

Hi snb, Thank you for the code but when running it halts on row - Trades.RemoveItem j. (Trades is the name of my listbox)
Is there a further setting I need to make.
Regards, Peter

[pcarmour]

Hi snb,
Although I have tried to see if I'm doing somthing wrong I am still having the code stop at: Trades.RemoveItem j. If you have a moment can you just have another look at the code.
Thank you, Peter.

[snb]

I can't open your file.....

[pcarmour]

Sorry that's the wrong file please ignore

[pcarmour]

Hi snb,
OK here is the file with the listbox that loads when you click Book Trade. What I now want is for the data selected in the listbox to be deleted from the sheet orders and the rows in that sheet moved up. Thanks again for your help. Regards, Peter.

Sorry if you can't see the file I'm now having trouble attaching!

[pcarmour]

Ok here is the file, it wouldn't attach as it had links I guess! Peter.

[snb]

Never use rowsource to populate a listbox/combobox

[pcarmour]

That's very interesting, you mean it should always be in VBA