Consulting

Results 1 to 4 of 4

Thread: ListBox priority Order

  1. #1
    VBAX Regular
    Joined
    Oct 2008
    Posts
    7
    Location

    ListBox priority Order

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Oct 2008
    Posts
    7
    Location
    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

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •