?
Solved

2 Column List Box VBA Move List Item Up/Down

Posted on 2014-02-11
5
Medium Priority
?
1,668 Views
Last Modified: 2014-02-12
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
Comment
Question by:DrTribos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 27

Expert Comment

by:MacroShadow
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

Open in new window

0
 
LVL 52

Expert Comment

by:Rgonzo1971
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

Open in new window

EDIT Exit For

Regards
0
 
LVL 15

Author Comment

by:DrTribos
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 52

Accepted Solution

by:
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))

Open in new window

Regards
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39852919
You suppose correct - many thanks!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question