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
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.
Nick Burns:
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.
14 June 2004, 2:28 pmDick:
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.
14 June 2004, 2:56 pmross:
Dick i think you have a list box fetish, the google results are are smoke screen!!!!!
15 June 2004, 3:27 amJamie Collins:
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.
–
15 June 2004, 5:10 amDick:
“Dick i think you have a list box fetish”
It looks like I’m busted.
15 June 2004, 9:40 amJames M:
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
28 December 2004, 8:48 amMathieu D.:
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?
9 August 2007, 10:16 amBekesi Z.:
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!
20 August 2007, 12:29 pmApirus:
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
12 October 2007, 9:38 amAndy Pope:
Hi Apirus,
You would get that error if you populated the listbox using the RowSource property.
16 October 2007, 1:50 amWhen using rowsource you can not change the order of the listbox using the above method. You would need to change the cells directly.
Jon Peltier:
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.
16 October 2007, 7:11 am