CODEWITHSUNDEEP
vbams-accesslistbox
MN_Freeze
MN_Freeze

Reputation: 1

Remove Item from List box (MS Access VBA)

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

Upvotes: 0

Views: 4240

Answers (2)

no spam
no spam

Reputation: 56

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.

Upvotes: 0

Erik A
Erik A

Reputation: 32642

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

Upvotes: 2

Related Questions