• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1397

# Use spin button to move items in listbox up and down

I have a list box with a numerous items. I want to put a spinbutton beside it so that the user can select and item to move up and down in the list. As they move it I want to adjust the data on the excel sheet accordingly. I know how to move the data. but i don't know how to link the spinbutton and control how to move the data as it is pressed up or down. Thanks
0
tchristie33
2 Solutions

Commented:
Try using the following code.  Change SpinButton1 to the name of the name of your spin button control and change ListBox1 to the name of your list box control.
``````Private Sub SpinButton1_SpinDown()
Call MoveListItem(True)
End Sub

Private Sub SpinButton1_SpinUp()
Call MoveListItem(False)
End Sub

Function MoveListItem(blnShiftDown As Boolean)

If ListBox1.ListIndex < 0 Or _
ListBox1.ListIndex = 0 And Not blnShiftDown Or _
ListBox1.ListIndex = ListBox1.ListCount - 1 And blnShiftDown Then Exit Function

Dim i As Integer
Dim strListItems() As String
ReDim strListItems(ListBox1.ListCount - 1)

For i = 0 To ListBox1.ListIndex - IIf(blnShiftDown, 1, 2)
strListItems(i) = ListBox1.List(i)
Next i
strListItems(i) = ListBox1.List(ListBox1.ListIndex + IIf(blnShiftDown, 1, 0))
strListItems(i + 1) = ListBox1.List(ListBox1.ListIndex - IIf(blnShiftDown, 0, 1))

For i = ListBox1.ListIndex + IIf(blnShiftDown, 2, 1) To ListBox1.ListCount - 1
strListItems(i) = ListBox1.List(i)
Next i

ListBox1.List = strListItems

End Function
``````
0

Commented:
You can swap List items using just a temp string. No intermediate array needed.
Following code moves List items Up or Down, checks for upper;lower bounds, and updates the ListIndex.

``````Option Explicit

Dim i As Long
For i = 1 To 10
ListBox1.AddItem "Item " & Chr(64 + i)
Next
End Sub

Private Sub SpinButton1_SpinDown()
Dim SelIndex         As Long
Dim Temp             As String

SelIndex = ListBox1.ListIndex
If (SelIndex >= 0) And (SelIndex < ListBox1.ListCount - 1) Then
Temp = ListBox1.List(SelIndex + 1)
ListBox1.List(SelIndex + 1) = ListBox1.List(SelIndex)
ListBox1.List(SelIndex) = Temp
ListBox1.ListIndex = ListBox1.ListIndex + 1
End If
End Sub

Private Sub SpinButton1_SpinUp()
Dim SelIndex         As Long
Dim Temp             As String

SelIndex = ListBox1.ListIndex
If SelIndex > 0 Then
Temp = ListBox1.List(SelIndex - 1)
ListBox1.List(SelIndex - 1) = ListBox1.List(SelIndex)
ListBox1.List(SelIndex) = Temp
ListBox1.ListIndex = ListBox1.ListIndex - 1
End If
End Sub
``````
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.