Remove Item from List box (MS Access VBA)

Question

I have a list box containing file names to be processing; multi-select option. The processing of files works. I would like for the list box to be updated after each iteration of the file processed, so that the file name is removed from the list box. The code below does remove the item selected, but then deselects all of the remaining items so the subsequent iterations of file processing are not executed. How do I remove the selected file from the list box of the current iteration but retain the selection of the other files and continue the process?

Many thanks.

Function RemoveListItem(ctlListBox As ListBox, ByVal varItem As Variant) As Boolean

Dim i As Integer

On Error GoTo ERROR_HANDLER
i = varItem
If ctlListBox.Selected(i) = True Then
    ctlListBox.RemoveItem (i)
End If

On Error GoTo 0
Exit Function


ERROR_HANDLER:
 RemoveListItem = False

End Function

This fills the list box:

Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
    Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)
On Error GoTo Err_Handler
    'Purpose:   List the files in the path.
    'Arguments: strPath = the path to search.
    '           strFileSpec = "*.*" unless you specify differently.
    '           bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
    '           lst: if you pass in a list box, items are added to it. If not, files are listed to immediate window.
    '               The list box must have its Row Source Type property set to Value List.
    'Method:    FilDir() adds items to a collection, calling itself recursively for subfolders.
    Dim colDirList As New Collection
    Dim varItem As Variant

    Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)

    'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
    For Each varItem In colDirList
        lst.AddItem varItem
    Next

Exit_Handler:
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler
End Function

UPDATING THE RemoveListItem TO THE CODE FROM COMMENTS:ERIK A....

as this Remove Item code works backward, I must then change my file processing to work backwards as well, i.e., the last file selected is the first file to be processed. I use the following to get through my file import process. It begins with the first file selected. How do I do this in reverse? Use the same logic/code as in the Remove Item?

For Each varItem In ctl.ItemsSelected
    strFileName = ctl.ItemData(varItem)

....{File Import Process}....

Next

It would help to know how the list box is populated, to begin with. Please use the edit link below the question to include that code, as well. — Cindy Meister
– Cindy Meister, Commented Dec 16, 2019 at 19:58
Since you're removing items from a collection you're presumably iterating, having the calling code for that function seems relevant, too. — Mathieu Guindon
– Mathieu Guindon, Commented Dec 16, 2019 at 20:08
I think you both asked for the same thing....the code to fill the list box has been added to the post. Thx. — MN_Freeze
– MN_Freeze, Commented Dec 16, 2019 at 20:16

Erik A · Accepted Answer · 2019-12-16 21:04:25Z

2

Changing the row source clears the selected items. This means you must store Selected, then remove in reverse order to prevent the positions from shifting:

Public Sub RemoveListItem(lstSource As ListBox)
    Dim intListX As Integer
    Dim selectedItems As Collection
    Set selectedItems = New Collection
    For intListX = lstSource.ListCount - 1 to 0 step -1
        If lstSource.Selected(intListX) Then
            selectedItems.Add intListX 'Add the items to be removed to a collection, in reverse order
        End If
        intListX = intListX - 1
    Loop
    Dim iterator As Variant
    For Each iterator In selectedItems
        lstSource.RemoveItem iterator 'And then remove them
    Next iterator
End Sub

This is slightly adapted from this answer

answered Dec 16, 2019 at 21:04

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

no spam · Accepted Answer · 2023-11-17 00:13:10Z

Thank You, @Erik A. You might want to remove line reading intListX = intListX - 1 as for loop already decrementing the variable. Here is your code slightly re-factored (; .

Public Sub RemoveSelectedListItems(lstSource As ListBox)
    Dim i As Integer
    Dim selectedItems As Collection
    Set selectedItems = New Collection
    For i = lstSource.ListCount - 1 To 0 Step -1
        If lstSource.Selected(i) Then
            selectedItems.Add i 'Add the items to be removed to a collection, in reverse order
        End If
    Loop
    Dim iterator As Variant
    For Each iterator In selectedItems
        lstSource.RemoveItem iterator 'And then remove them
    Next iterator
    Set selectedItems = Nothing
End Sub

Thanks.

Collectives™ on Stack Overflow

Remove Item from List box (MS Access VBA)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related