How do I implement Move UP/Move Down functionality in a list in a userform

Question

A number of built-in dialog boxes (like those for adding icons to the quick access toolbar) allow you to sort/rearrange the order of a list by selecting an element of the list and moving it up or down within the list. I have a number of lists in a spreadsheet used to populate drop down list boxes and for data validation. I want to create a utility to allow users to select a list to edit, edit the lists (add, delete, rearrange the order) and save the list back to the original location without having to unhide the worksheets containing the lists.

I will use dynamic drop downs to select the list to be editted, but I don't know how to implement the Move Up/Move Down functionality. Can someone point me in the right direction? 

---

Neil

Answer 1

See for example http://www.xtremevbtalk.com/archive/index.php/t-180834.html.

Answer 2

Here is a method that I have posted in the past that allows you to drag and drop items within a ListBox (on a UserForm). To move an item, press the Shift key (think of the Shift key as a memory aid... Shift for shifting items), either before or after selecting your item , click on the item and drag the highlight to the position where you want that item to be located at. The rule for how the items move out of the way is this... if you move the item to a location earlier in the list than where it currently is, the item you drop it on (and all items following it) moves down the list to make room for the item... if you move the item to a location later in the list than its where it currently is, the item you drop it on (and all preceding items) moves up the list to make room for it. This movement rule allows you to move the item to either end without problem (you would not be able to slot the item at one end or the other if the list always moved in the same direction). Okay, just paste the following code into the UserForm's code window (see additional comments after the code)...

'*************** START OF CODE ***************

Dim DragIndex As Long

Dim SecondTime As Boolean

Dim DragActive As Boolean

Dim MouseDownFlag As Boolean

Private Sub ListBox1_Click()

  With ListBox1

    If SecondTime Then

      SecondTime = False

      Exit Sub

    ElseIf MouseDownFlag Then

      DragIndex = .ListIndex

      MouseDownFlag = False

      DragActive = True

      SecondTime = True

    End If

  End With

End Sub

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

  With ListBox1

    If Shift = 1 Then

      MouseDownFlag = True

      .ListIndex = -1

    End If

  End With

End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Dim ListText As String

  With ListBox1

    If DragActive Then

      ListText = .List(DragIndex)

      .RemoveItem DragIndex

      .AddItem ListText, .ListIndex - (.ListIndex > DragIndex)

      For X = 0 To .ListCount - 1

        If .List(X) = ListText Then

          .ListIndex = X

          Exit For

        End If

      Next

    End If

    DragActive = False

  End With

End Sub

'*************** END OF CODE ***************

If you want to test this out on a brand new UserForm, just place a ListBox on the UserForm and use this event procedure to load up the ListBox with sample data to drag around (don't forget to press the Shift key while dragging)...

Private Sub UserForm_Initialize()

  Dim X As Long

  With ListBox1

    For X = 0 To 30

      .AddItem "Item #" & CStr(X + 1)

    Next

  End With

End Sub

Note... I put the ListBox name in With statements to make it easier to change the referenced list box if you used a ListBox with a different name than ListBox1. You would have to change the event declaration statements and the With statements changing my example name of ListBox1 to whatever name you gave your ListBox.

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 3

I have an example workbook at http://www.cpearson.com/zips/ListboxUtils.zip that has code for a dozen or so common functions for list boxes, including moving items up and down within the list.

---

Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com

Answer 4

I have an example workbook at http://www.cpearson.com/zips/ListboxUtils.zip that has code for a dozen or so common functions for list boxes, including moving items up and down within the list.

---

Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com

Your code will do more than I thought to do with my procedure, but I can't run the demo on the machines at work because "Programatic access to Visual Basic Project is not trusted." I can change the trust settings on my PC at home, but not in the office.

It appears that the Project access is only needed to display the procedure names on the demo form. Is that correct?

---

Neil

Answer 5

Thanks for the link. I will incorporate the code and then post whether or not it worked.

---

Neil