Reordering with Multicolumn Listbox

[Guest]

I know it is possible to use a listbox (single column) to resort the order
(move-up / Move-down). But I am trying to accomplish this with a
multi-column listbox. But everytime I select a row and press the Move-Up
button, I get a permission denied error (error code 70). And my worksheet
and book are unprotected. Any Ideas?

Private Sub CommandUp_Click()
If ListBox1.ListIndex <= 0 Then Exit Sub
NumItems = ListBox1.ListCount
Dim TempList()
ReDim TempList(0 To NumItems - 1)
'Fill the List Box
For i = 0 To NumItems - 1
TempList(i) = ListBox1.List(i)
Next i
' Selected Item
ItemNum = ListBox1.ListIndex
'Exchange Items
TempItem = TempList(ItemNum)
TempList(ItemNum) = TempList(ItemNum - 1)
TempList(ItemNum - 1) = TempItem
ListBox1.List = TempList
'Change the Index List
ListBox1.ListIndex = ItemNum - 1

End Sub

Private Sub UserForm_Initialize()
'Fill the List Box
ColCnt = ActiveSheet.Columns.Count
Set Rng = ActiveSheet.Range("A21:AD53")
With ListBox1
.ColumnCount = ColCnt
.RowSource = Rng.Address
cw = ""
For c = 1 To .ColumnCount
cw = cw & Rng.Columns(c).Width & ";"
Next c
.ColumnWidths = cw
.ListIndex = 0
End With

End Sub

 

[Dick Kusleika]

asmenut

You can't set the List property to an array if you've used the RowSource
property. Using List and AddItem is one way to fill a ListBox and using
RowSource is another - you can't use both. I suggest that you don't use
RowSource and populate the ListBox using AddItem. You will have a lot more
flexibility.

See here for populating a multi-column listbox

http://www.dicks-blog.com/excel/2004/05/populating_mult.html

See here for moving up and down

http://www.dicks-blog.com/excel/2004/06/move_upmove_dow.html

 

[Guest]

Thank you sir.

asmenut

You can't set the List property to an array if you've used the RowSource
property. Using List and AddItem is one way to fill a ListBox and using
RowSource is another - you can't use both. I suggest that you don't use
RowSource and populate the ListBox using AddItem. You will have a lot more
flexibility.

See here for populating a multi-column listbox

http://www.dicks-blog.com/excel/2004/05/populating_mult.html

See here for moving up and down

http://www.dicks-blog.com/excel/2004/06/move_upmove_dow.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com