Solved

2 Column List Box VBA Move List Item Up/Down

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

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 51

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

To Patch or not to Patch? That is the question!

Don't get caught out like thousands of others around the world in the recent Ransomware Fiasco!
Discuss..
- Why it's not a good idea to wait before Patching
- Sensible approaches to Patching discussed
- Add your feedback, comments and suggestions

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

739 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