Move Up/Move Down in a ListBox

Are you sick of reading about listboxes yet? I sure get a lot of google searches for them, so I guess I’ll keep blogging about them.

This post shows how to let the user reorder items in a listbox with command buttons. You can see an example of this in the Visual Basic Editor under Tools>References. That dialog has priority buttons where you can move items in the listbox up or down to set their priority.

Take this multi-column listbox as an example

ReOrderLb1

The code for the command buttons is as follows:

Private Sub cmdDown_Click()

MoveItem 1

End Sub

Private Sub cmdUp_Click()

MoveItem -1

End Sub

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

This should work for listboxes with any number of columns. The Caption properties for the two command buttons are 0219 and 0220 in the Wingdings3 font. To use captions like these, show the Properties box (F4) and in the Caption property hold down the Alt key and type 0219 on the numeric keypad. Of course, change the Font property also.

22 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. 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.

    –

  4. 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

  5. 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?

  6. 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!

  7. 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

  8. 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.

  9. 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.

  10. 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

  11. 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/

  12. 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

  13. 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)

  14. 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 :(

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

Leave a Reply

Your email address will not be published. Required fields are marked *