ListBox priority Order

[tonyam]

Ok i have a listbox with products in it, and i have two command buttons with up and down arrows/text on them. I want the user to be able to select a product and move it up and down the list to show their priority for the particular products. All the above is in a userfrom form in excel and edited with VBA.

Can anyone advise the easiest way to set the code up for this please?


Thanks in advance:beerchug:

[Bob Phillips]

Private Sub cmdUp_Click()
Dim tmp As Variant

With Me

With .ListBox1

If .ListIndex > 0 Then

tmp = .Value
.List(.ListIndex) = .List(.ListIndex - 1)
.List(.ListIndex - 1) = tmp
.ListIndex = .ListIndex - 1
End If
End With
End With
End Sub

Private Sub cmdDown_Click()
Dim tmp As Variant

With Me

With .ListBox1

If .ListIndex + 1 < .ListCount Then

tmp = .Value
.List(.ListIndex) = .List(.ListIndex + 1)
.List(.ListIndex + 1) = tmp
.ListIndex = .ListIndex + 1
End If
End With
End With
End Sub

[tonyam]

Thats great, thanks for your help. Looks like that should work, but as the sheet that feeds the listbox is password protected it is bringing up an error. Can you tell me how to unprotect the sheet when the command buttons are clicked, and then reprotect it on exit?
Thanks again

[Bob Phillips]

If you are uysing a worksheet range, you need a different approach



Private Sub cmdUp_Click()
Dim ws As Worksheet
Dim tmp As Variant

Set ws = Worksheets("Sheet1")
ws.Unprotect Password:="mypassword"

With Me

With .ListBox1

If .ListIndex > 0 Then

tmp = .Value
ws.Range(.RowSource).Cells(.ListIndex + 1, 1).Value = _
ws.Range(.RowSource).Cells(.ListIndex, 1).Value
ws.Range(.RowSource).Cells(.ListIndex + 1, 1).Value = tmp
' .ListIndex = .ListIndex - 1
End If
End With
End With

ws.Protect Password:="mypassword"
Set ws = Nothing
End Sub

Private Sub cmdDown_Click()
Dim ws As Worksheet
Dim tmp As Variant

Set ws = Worksheets("Sheet1")
ws.Unprotect Password:="mypassword"

With Me

With .ListBox1

If .ListIndex + 1 < .ListCount Then

tmp = .Value
ws.Range(.RowSource).Cells(.ListIndex + 1, 1).Value = _
ws.Range(.RowSource).Cells(.ListIndex + 2, 1).Value
ws.Range(.RowSource).Cells(.ListIndex + 2, 1).Value = tmp
.ListIndex = .ListIndex + 1
End If
End With
End With

ws.Protect Password:="mypassword"
Set ws = Nothing
End Sub