Solved

Use spin button to move items in listbox up and down

Posted on 2008-10-09
915 Views
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
Question by:tchristie33

LVL 23

Accepted Solution

irudyk earned 250 total points
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

LVL 22

Assisted Solution

danaseaman earned 250 total points
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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…