Solved

2 Column List Box VBA Move List Item Up/Down

Posted on 2014-02-11
5
1,535 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
  • 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 49

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 14

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 49

Accepted Solution

by:
Rgonzo1971 earned 500 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 14

Author Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

919 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now