Improve company productivity with a Business Account.Sign Up

x
?
Solved

2 Column List Box VBA Move List Item Up/Down

Posted on 2014-02-11
5
Medium Priority
?
1,798 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 28

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 55

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 55

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This is a comprehensive review of a bundled Toolkit designed for use by IT Professionals and End Users to help Microsoft Outlook fans manipulate Outlook files and repair some common problems. Enjoy...
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

586 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