x
Solved

# 2 Column List Box VBA Move List Item Up/Down

Posted on 2014-02-11
Medium Priority
1,798 Views
Hi All,

I can probably nut this out but struggling and need the answer quick.  What I have is a 2 column list box - typically less than 10 items.

I have a spin up and spin down button.

I select 1 item at a time.  Then I want to use spin up to advance the item in the list OR spin down to move the item down the list.

If anyone can provide some robust code for this I'd be pretty happy.

Thanks.
0
Question by:DrTribos
• 2
• 2

LVL 28

Expert Comment

ID: 39852517
Try this:

``````Private Sub cmd_Up_Click()

Dim i As Long
Dim leaveAlone As Boolean
Dim pos As Long
Dim Temp As String

pos = 0
For i = 0 To ListBox1.ListCount - 1
leaveAlone = False
If ListBox1.Selected(i) Then
If i = pos Then
leaveAlone = True
End If
pos = pos + 1
If leaveAlone = False Then
Temp = ListBox1.List(i - 1)
ListBox1.List(i - 1) = ListBox1.List(i)
ListBox1.List(i) = Temp
ListBox1.ListIndex = i - 1
ListBox1.Selected(i) = False
ListBox1.Selected(i - 1) = True
End If
End If
Next

End Sub

Private Sub cmd_Down_Click()

Dim i As Integer
Dim leaveAlone As Boolean
Dim pos As Long
Dim Temp As String

pos = ListBox1.ListCount - 1
For i = ListBox1.ListCount - 1 To 0 Step -1
leaveAlone = False
If ListBox1.Selected(i) Then
If i = pos Then
leaveAlone = True
End If
pos = pos - 1
If Not leaveAlone Then
Temp = ListBox1.List(i + 1)
ListBox1.List(i + 1) = ListBox1.List(i)
ListBox1.List(i) = Temp
ListBox1.ListIndex = i + 1
ListBox1.Selected(i) = False
ListBox1.Selected(i + 1) = True
End If
End If
Next

End Sub
``````
0

LVL 55

Expert Comment

ID: 39852767
Hi,

pls try for multicolum listbox
``````Private Sub SpinButton1_SpinDown()
MoveItemListBox myListBox1, 1
End Sub

Private Sub SpinButton1_SpinUp()
MoveItemListBox myListBox1, -1
End Sub

Private Sub MoveItemListBox(ByVal lstBx, lOffset As Long)

Dim i As Long, i2 As Long
Dim leaveAlone As Boolean
Dim pos As Long
Dim lstBgn As Long, lstEnd As Long
Dim Temp As String

If Sgn(lOffset) = 1 Then
lstBgn = UBound(lstBx.List)
lstEnd = LBound(lstBx.List)
Else
lstBgn = LBound(lstBx.List)
lstEnd = UBound(lstBx.List)
End If

pos = lstBgn

With lstBx
For i = lstBgn To lstEnd Step -Sgn(lOffset)
If .Selected(i) Then
If i = pos Then Exit For

For i2 = 0 To .ColumnCount - 1
Temp = .List(i + lOffset, i2)
.List(i + lOffset, i2) = .List(i, i2)
.List(i, i2) = Temp
.ListIndex = i + lOffset
.Selected(i) = False
.Selected(i + lOffset) = True
Next
End If
Next
End With

End Sub
``````
EDIT Exit For

Regards
0

LVL 15

Author Comment

ID: 39852890
Hi MacroShadow - sorry it might not have been clear in the OP but both columns need to move together.  Thanks for the suggestion - unfortunately it only moves the left column.

Hi Rgonzo - I'm getting an error on line 33 of your code "Invalid use of Null" RTE 94 which I think is due to the fact that I have some blank entries in the 2nd list... I think I can adapt.
0

LVL 55

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 39852897
Hi,

I suppose that would resolve the problem
``````Temp = IIf(IsNull(.List(i + lOffset, i2)), "", .List(i + lOffset, i2))
``````
Regards
0

LVL 15

Author Comment

ID: 39852919
You suppose correct - many thanks!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.