23 thoughts on “Move Up/Move Down in a ListBox”

1. 2 Comments:

   1) You can use a SpinButton control and assign the MoveItem routine to the spnButton_SpinUp() and spnButton_SpinDown() events as needed.

   2)Also extra coding is needed to test when the selected item is either at the top of the list or at the bottom depending on which button is pressed.

   Just my 2c worth. :-D

2. Nick: I like the SpinButton idea, I hadn’t thought of that. As for the extra coding, I always disable buttons instead of coding for errors. If the first item is selected, I prefer that the Up button be disabled.

3. Dick i think you have a list box fetish, the google results are are smoke screen!!!!!

4. For the listbox fetishist, lots of great things can be done using Win32 APIs, provided you can get a handle of course. Best I can do is get a handle to the owner. This code works for a userform (Excel 2000 and above) containing a *single* listbox (more complex code required to enumerate windows to find the ID of the one required):

   Option Explicit

   Private Declare Function FindWindow _
   Lib “user32? Alias “FindWindowA” ( _
   ByVal lpClassName As String, _
   ByVal lpWindowName As String) As Long
   Private Declare Function FindWindowEx _
   Lib “user32? Alias “FindWindowExA” _
   (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
   ByVal lpsz1 As String, _
   ByVal lpsz2 As String) As Long

   Sub Userform_Initialize()

   Dim hForm As Long
   Dim hFormControlArea As Long
   Dim hControl As Long

   Const CLASS_USERFORM As String = “ThunderDFrame”
   Const CLASS_CONTROL As String = “F3 Server 60000000?

   ‘ Get handle to listbox
   hForm = FindWindow(CLASS_USERFORM, Me.Caption)
   hFormControlArea = FindWindowEx(hForm, ByVal 0&, _
   CLASS_CONTROL, vbNullString)
   hControl = FindWindowEx(hFormControlArea, ByVal 0&, _
   CLASS_CONTROL, vbNullString)

   Debug.Print hForm, hFormControlArea, hControl

   End Sub

   For the resulting handle, at least some of the listbox messages seem to take. For example, I seemingly go this code to work:

   http://vbnet.mvps.org/index.html?code/listapi/listrightalign.htm

   Jamie.

   –

5. “Dick i think you have a list box fetish”

   It looks like I’m busted.

6. To eliminate any error when you have reached the bottm or top of the list. Simply use the following code in your MoveItem Sub:

   On error goto EndSub

   ‘Existing Code

   EndSub: ‘Place this label just before End Sub

7. I’m trying to get the selected property to follow everything once it’s done. Your code moves the selected item up and down, but it doesn’t follow accordingly. Would there be anyway to do it?

8. To get the selected property follow insert following line above the next i statement ind the MoveItem Sub:

   .ListIndex = .ListIndex + lOffset

   This code is really great. I was just looking for something like this and my first search got me here. Thank you!

9. I have been trying to reorder rows in a list box.
   I am getting an error

   “Run-Time Error ’70’ .Could not set the list property. Permission denied.

   Any suggestions please

10. Hi Apirus,

    You would get that error if you populated the listbox using the RowSource property.
    When using rowsource you can not change the order of the listbox using the above method. You would need to change the cells directly.

11. I never use the RowSource property to populate a control. My calling code feeds the form an array using a public let property. Often the array is simply taken from a worksheet range, but this is much more flexible than locking myself into the range. If I need the worksheet to change, well, the form feeds the array back to the calling code using a public get property.

12. I wanted to make it move sheets and have com up with the following. Perhaps that someone else likes this too!

    Private Sub UserForm_Initialize()

    Dim sVal As String
    Dim dataSheet As Worksheet
    Dim iDataRow As Long

    Set dataSheet = Sheets(“Sheet1?)

    ‘Fill in the listbox
    lbxTeams.Clear
    ‘Column A contains the item list (Row 1 is the column header)
    iDataRow = 2 ‘first item in the list
    Do While Len(dataSheet.Range(“A” & iDataRow).Text) > 0
    sVal = dataSheet.Range(“A” & iDataRow).Text
    lbxTeams.AddItem sVal
    iDataRow = iDataRow + 1
    Loop
    End Sub
    Private Sub cmdDown_Click()

    Dim ShtIndex As Integer
    Dim AftrSht As String

    ‘Move Item in ListBox
    MoveItem 1

    ‘Get Values for Moving Sheets
    ShtIndex = lbxTeams.ListIndex
    AftrSht = lbxTeams.List(ShtIndex – 1, 0)

    ‘Move After Sheet…
    Sheets(lbxTeams.Value).Move After:=Sheets(AftrSht)

    End Sub
    Private Sub cmdUp_Click()

    Dim ShtIndex As Integer
    Dim BfrSht As String

    ‘Move Item in ListBox
    MoveItem -1

    ‘Get Values for Moving Sheets
    ShtIndex = lbxTeams.ListIndex
    BfrSht = lbxTeams.List(ShtIndex + 1, 0)

    ‘Move Before Sheet…
    Sheets(lbxTeams.Value).Move Before:=Sheets(BfrSht)

    End Sub

    Private Sub MoveItem(lOffset As Long)

    Dim aTemp() As String
    Dim i As Long

    On Error GoTo EndSub

    With Me.lbxTeams
    If .ListIndex > -1 Then
    ReDim aTemp(0 To .ColumnCount – 1)
    For i = 0 To .ColumnCount – 1
    aTemp(i) = .List(.ListIndex + lOffset, i)
    .List(.ListIndex + lOffset, i) = .List(.ListIndex, i)
    .List(.ListIndex, i) = aTemp(i)
    .ListIndex = .ListIndex + lOffset
    Next i
    End If
    End With

    EndSub: End Sub

13. Private Sub MoveItem(lOffset As Long)
       
        Dim aTemp() As String
        Dim i As Long
       
        With Me.lstToInclude
          If .ListIndex > -1 Then
            ReDim aTemp(0 To .ColumnCount – 1)
            For i = 0 To .ColumnCount – 1
              aTemp(i) = .List(.ListIndex + lOffset, i)
              .List(.ListIndex + lOffset, i) = .List(.ListIndex, i)
              .List(.ListIndex, i) = aTemp(i)
            Next i
          End If
          .Selected(.ListIndex) = False
          .ListIndex = .ListIndex + lOffset
          .Selected(.ListIndex) = True
        End With
     
    End Sub

    http://www.codeforexcelandoutlook.com/blog/2009/02/random-sample-data-generator-add-in-for-excel-now-available/

14. Hey Dick –

    I updated the add-in and should be re-released it again later today. Thanks again for your help.

    –JP

15. JP Awesome. This is going to a very useful add-in, I think. I’ve already got some ideas for the next version (e.g. custom fields).

16. Feel free to suggest. I’m working on an Outlook version of my Post to Twitter add-in, I can add to this one as well.

    Thx

17. Let the user define new fields. They name it and make it a

    1) Number – Min, Max
    2) String – Length, case(proper, lower, upper)
    3) Regex – They put in a regular expression and you output a string that would pass it
    4) Date – Min, Max (or split into day, month, year with separate criteria for each)

18. Nice. I’ll make a list and start working on this next week.

19. Pingback: Random sample data generator add-in for Excel now available - JP Software Technologies

20. Using Same Code for moving the items in the list as below :
    Private Sub MoveItem(lOffset As Long)

    Dim aTemp() As String
    Dim i As Long

    With Me.lbxTeams
    If .ListIndex > -1 Then
    ReDim aTemp(0 To .ColumnCount – 1)
    For i = 0 To .ColumnCount – 1
    aTemp(i) = .List(.ListIndex + lOffset, i)
    .List(.ListIndex + lOffset, i) = .List(.ListIndex, i)
    .List(.ListIndex, i) = aTemp(i)
    Next i
    End If
    End With
    End Sub

    Getting error on .List ( Mathod or Data Member Not Found ). Any Help :(

21. If lbxTeams isn’t a listbox or combobox, then it won’t have a .List property. That’s the first thing I’d check.

22. Pingback: Take ListBox Data and put it on spreadsheet

23. 1) Added “exit sub” to avoid errors.
    2) selection of the moved item (otherwise it repeats the same pair replace again and again)
    3) Used SpinButtons.

    Private Sub SpinButton1_SpinDown()
    With Me.ListBoxMoveUpDown
    If .ListIndex = -1 Then Exit Sub ‘no ValidSelection Made
    If .ListIndex = .ListCount – 1 Then Exit Sub ‘if already at the bottom Exit Sub
    End With

    MoveItem 1
    End Sub

    Private Sub SpinButton1_SpinUp()
    With Me.ListBoxMoveUpDown
    If .ListIndex = -1 Then Exit Sub ‘no ValidSelection Made
    If .ListIndex = 0 Then Exit Sub ‘if already at the top Exit Sub
    End With

    MoveItem -1
    End Sub

    Private Sub MoveItem(lOffset As Long)
    Dim aTemp() As String
    Dim i As Long

    With Me.ListBoxMoveUpDown
    If .ListIndex > -1 Then
    ReDim aTemp(0 To .ColumnCount – 1)
    For i = 0 To .ColumnCount – 1
    aTemp(i) = .List(.ListIndex + lOffset, i)
    .List(.ListIndex + lOffset, i) = .List(.ListIndex, i)
    .List(.ListIndex, i) = aTemp(i)
    Next i
    End If

    .Selected(.ListIndex + lOffset) = True
    End With

    End Sub

Posting code? Use <pre> tags for VBA and <code> tags for inline.