How to move item in listBox up and down access VBA? (1 Viewer)

[svuyyuru]

I have a list box contains 10 times . I have UP and Down buttons to move item up and down. My VBA works only if i set listbox multiselect property to 'None'. For the multiselect=simple option it throws error like in valid use of null in this line of code

sText = lbfNames.Column(0, iIndex)

My VBA

Private Sub cmdUP_Click() 
    Dim sText As String
       Dim iIndex As Integer
       iIndex = lbfNames.ListIndex
       'check: only proceed if there is a selected item
       If lbfNames.ListCount > 1 Then
         'index 0 is top item which can't be moved up!
        If iIndex <= 0 Then
            MsgBox ("Can not move the item up any higher.")
            Exit Sub
        End If
        ' If iIndex = -1 Or lbfNames.ListCount > 1 Then
        'save items text and items indexvalue
        sText = lbfNames.Column(0, iIndex)
        lbfNames.RemoveItem iIndex
        'place item back on new position
        lbfNames.AddItem sText, iIndex - 1
        'if you keep that item selected
        'you can keep moving it by pressing cmdUp
        lbfNames.Selected(iIndex - 1) = True
        iIndex = iIndex - 1
   End If
   End sub

 

[burrina]

My question is why do you need to do this? Is sorting not sufficient?

 

[svuyyuru]

My user wants this functionality, even the sorting order does not matter.

 

[micks55]

This is not as easy as you might imagine. You'll need a field in the underlying table that is the Row Source for the list box. This field will be Numeric (Integer) and could be called Seqnc. The field will be included in the list box Row Source but with a 0cm column width so that it's hidden (set it to 0.5cm for now so you can see it during development then 0cm later). Next you will need code that adds or subtracts 0.9 to/from this value when the up/down key is pressed and then you will need code that opens the recordset in Seqnce order and renumbers the Seqnc field incrementally starting with 1 and adding 1 for each subsequent record until it reaches EOF. Finally you will requery the list box.

 

[burrina]

Try this, of course change the name(s) to match yours.

Function fncSelectNextInListbox( _
lst As Access.ListBox, _
Optional bWrap As Boolean) _
As Long

' Select the next item in the list box passed as .
' If no item is selected, select the first one.
' If the optional argument is True, then the selection
' will wrap around from the last item in the list box to the
' first item.

' NOTE: THIS FUNCTION ONLY WORKS FOR NON-MULTISELECT LIST BOXES.

' Copright (c) 2009, DataGnostics LLC.
' You are free to use this code in your application, so long
' as the copyright notice remains unchanged.

Dim lngMaxIndex As Long
Dim lngFirstIndex As Long

With lst

If .ListCount <> 0 Then

lngFirstIndex = Abs(.ColumnHeads)
lngMaxIndex = .ListCount - lngFirstIndex - 1

If .ItemsSelected.Count = 0 Then
.Value = .ItemData(lngFirstIndex)
Else
If .ListIndex >= lngMaxIndex Then
' We're at the end of the list box.
If bWrap Then
.Value = .ItemData(lngFirstIndex)
End If
Else
.Value = .ItemData(.ListIndex + lngFirstIndex + 1)
End If
End If

End If

fncSelectNextInListbox = .ListIndex

End With

End Function


Function fncSelectPreviousInListbox( _
lst As Access.ListBox, _
Optional bWrap As Boolean) _
As Long

' Select the previous item in the list box passed as .
' If no item is selected, select the first one.
' If the optional argument is True, then the selection
' will wrap around from the first item in the list box to the
' last item.

' NOTE: THIS FUNCTION ONLY WORKS FOR NON-MULTISELECT LIST BOXES.

' Copright (c) 2009, DataGnostics LLC.
' You are free to use this code in your application, so long
' as the copyright notice remains unchanged.

Dim lngMaxIndex As Long
Dim lngFirstIndex As Long

With lst

If .ListCount <> 0 Then

lngMaxIndex = .ListCount - 1
lngFirstIndex = Abs(.ColumnHeads)

If .ItemsSelected.Count = 0 Then
.Value = .ItemData(lngMaxIndex)
Else
If (.ListIndex + lngFirstIndex) <= lngFirstIndex Then
' We're at the top of the list box.
If bWrap Then
.Value = .ItemData(lngMaxIndex)
End If
Else
.Value = .ItemData(.ListIndex - 1 + lngFirstIndex)
End If
End If

End If

fncSelectPreviousInListbox = .ListIndex

End With

End Function
'------ end of code ------

 

[burrina]

Good Luck With Your Project. Sorry, I have never tried that option before.I would have to do some thinking on the mater.

 

[svuyyuru]

Thanks for the samples. But those are working only for Non-Multiselect listbox , not working if i change listbox property to Multiselect = sample.

 

[burrina]

I have never tried the other before. Sorry, will have to simmer on that a bit.

 

[burrina]

Okay, here is my approach for this. It may not be pretty or practical though. I would use a second ListBox with no Multi Select and then sort, then run a qry to update the other ListBox.

HTH