Reordering with Multicolumn Listbox
 

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

Reordering with Multicolumn Listbox
 

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...ting_mult.html

See here for moving up and down

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

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

"asmenut" wrote in message
...
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

Reordering with Multicolumn Listbox
 

Thank you sir.

"Dick Kusleika" wrote:

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...ting_mult.html

See here for moving up and down

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

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

"asmenut" wrote in message
...
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