Solved

2 Column List Box VBA Move List Item Up/Down

Posted on 2014-02-11
5
1,558 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 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 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 15

Author Comment

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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dynamic Chart Range 13 36
Excel error  #DIV/0! 7 18
Excel - remove duplicates 1 13
excel count months in date range 6 12
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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 …

772 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