PDA

View Full Version : Selecting multi Rows and Colums in a List Box



pcarmour
11-29-2012, 01:45 PM
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
11-29-2012, 02:37 PM
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

pcarmour
11-29-2012, 03:10 PM
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
11-29-2012, 03:35 PM
Did you try the last version in #2 ?

pcarmour
11-29-2012, 03:39 PM
Yes, your version 2 tried

snb
11-29-2012, 03:40 PM
Did you select more than 1 item in the listbox ?
I can't check your workbook from a distance...

pcarmour
11-29-2012, 03:47 PM
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
11-29-2012, 04:00 PM
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
11-30-2012, 01:36 AM
I can't replicate your findings.
Cfr. the attachment.

pcarmour
11-30-2012, 03:14 PM
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
12-01-2012, 02:31 AM
Hi, One further question, how can I cut the selection in the List Box rather than copy?
Peter.

snb
12-01-2012, 05:46 AM
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

pcarmour
12-01-2012, 01:44 PM
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
12-03-2012, 06:03 AM
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
12-03-2012, 07:32 AM
I can't open your file.....

pcarmour
12-03-2012, 12:26 PM
Sorry that's the wrong file please ignore

pcarmour
12-03-2012, 12:48 PM
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
12-03-2012, 01:11 PM
Ok here is the file, it wouldn't attach as it had links I guess! Peter.

snb
12-03-2012, 01:38 PM
Never use rowsource to populate a listbox/combobox

pcarmour
12-03-2012, 02:04 PM
That's very interesting, you mean it should always be in VBA

snb
12-03-2012, 02:15 PM
Use .List to populate a listbox/combobox.
Rowsource prevents adapting a listbox/combobox.

pcarmour
12-06-2012, 05:12 AM
Hi snb,
Sorry again for not explaining my cut instead of copy question clearly, what I am trying to do is delete the selected list box entries from the source sheet and move the remaining items in the source sheet up.

pcarmour
12-07-2012, 02:45 AM
HI snub,
I'm sorry if my request regarding cut instead of paste wasn't totally clear. What I am trying to achieve is for the selections in the listBox to be deleted (cut) from the source work sheet and the remaining items in that worksheet to be moved up. Also with regard to rowsource can you give me the correct code to replace the userform rowsource - which does seem to be working ok at the moment.

Thanks in advance,

pcarmour
12-10-2012, 04:43 AM
AA