Sleeper: Moving Items in a ListBox

[yn19832]

I have built a listbox named ListCT, and I want to move up the selected item with the "Move up" Button. The list box contain two columns and the codes are as following:



Private Sub UpButton_Click()
If LiqForm.ListCT.ListIndex <= 0 Then Exit Sub
NumItems = LiqForm.ListCT.ListCount
Dim Templist()
ReDim Templist(0 To NumItems - 1, 0 To 1)
'Selected Item
Item = LiqForm.ListCT.ListIndex
'Fill Array with list box items
For i = 0 To NumItems - 1
Templist(i, 0) = LiqForm.ListCT.List(i, 0)
Templist(i, 1) = LiqForm.ListCT.List(i, 1)
Next i
'Exchange Item
TempItem0 = Templist(Item, 0)
TempItem1 = Templist(Item, 1)
Templist(Item, 0) = Templist(Item - 1, 0)
Templist(Item, 1) = Templist(Item - 1, 1)
Templist(Item - 1, 0) = TempItem0
Templist(Item - 1, 1) = TempItem1
LiqForm.ListCT.List(i, 0) = Templist(i, 0)
LiqForm.ListCT.List(i, 1) = Templist(i, 1)
'Change the list index
LiqForm.ListCT.ListIndex = Item - 1
End Sub

But when I run the codes, the following error comes out
Subscript out of range (Error 9)

When I debug, it comes to LiqForm.ListCT.List(i, 0) = Templist(i, 0)

Can anyone help me sort it out?

[mdmackillop]

Is this (http://www.vbaexpress.com/forum/showthread.php?t=10233&highlight=listbox) any help?

[moa]

yn19832, you need to reset the value of i. it is 1 more than you need it to be because it has gone through the loop up to NumItems -1, done its thing, then hit 'next', added one more to i, then exited the loop as it no longer meets the criterea.

i=i-1 after the loop would fix it but I still don't see the point in an array when the list in the listbox is pretty much an array anyway.

[yn19832]

Is this (http://www.vbaexpress.com/forum/showthread.php?t=10233&highlight=listbox) any help?

It is exactly what I want, many thanks

[mdmackillop]

Glad to help.
BTW, when you post code, please select it and click the VBA button, which formats it as shown
Regards
MD

[yn19832]

Now I have another question, I want to transfer the array "varArray" to the shee1 with the following codes, but it turned out to be a mass. The data in "tblIndex" is like this

Date Country Type Index
31/03/1980 Canada A 1
... ... ... ...


Set rec = db.OpenRecordset("tblIndex", dbOpenDynaset)
If Not rec.EOF Then
rec.MoveLast
rec.MoveFirst
intRecord = rec.RecordCount
varArray = rec.GetRows(intRecord)
intFieldCount = UBound(varArray, 1)
intRowCount = UBound(varArray, 2)
' Make Sure Sheet1 is Activate
Sheets("Sheet1").Activate
' Set worksheet range
Set TheRange = ActiveCell.Range(Cells(1, 1), Cells(intRowCount + 1, intFieldCount + 1))
' Copy the record to Excel
TheRange.Value = varArray

Could you pls help me with it?

[mdmackillop]

Here's an example using data from the spreadsheet to create the array.