Printable View
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:
[vba]
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
[/vba]
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
If you are uysing a worksheet range, you need a different approach
[vba]
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
[/vba]