Access: Multi-Listbox - Select items if they equal a specific string using VBA

Question

I have a form in access that has a listbox called 'lstLoadedFiles' which displays a list of all files that have been uploaded to my database.

This listbox contains 3 columns and is a multiselect listbox, so the user can select more than 1 item from the list at once.

The lisbox looks something like the below:

Type        FileName            Status
Blue        Bluefile1.xls       Loaded
Blue        Bluefile2.xls       Loaded
Red         Redfile3.xls        Loaded
Green       Greenfile1.xls      Loaded

The user can then select multiple records in this listbox and then click a button and code will run to validate the files for all items that have been selected.

This listbox can contain up to 20 filenames. I want the user to be able to select a button and it will highlight all records in the listbox related to a certain type e.g. 'Blue'. (This is needed so if a user only has all the relevant files for one type they can move through the process without having to wait for the remainder files for the others types - and they don't have to individually select all files from the listbox for the one type eg blue)

My problem is that the items in this listbox will never be in the same order and will never have the same number of files (eg 3 files may be uploaded for type blue, but the next time they run the process only 1 file might be uploaded) so using something like the following will not work:

Forms("MyForm").lstLoadedFiles.Selected(0) = True

Is there a way that I can code it to have something like the following:

If optSelectAllBlue = True Then
    lstLoadedFiles. Select Items where column type = 'Blue' .....

where optSelectAllBlue would be a button. Or is this not possible with a listbox?

Thanks in advance,

June7 June7 · Accepted Answer · 2020-08-28T17:34:56

Loop through listbox items and test if value = selected color and if match select item. The trick is figuring out how to make color value dynamic. If you are using an OptionGroup with only 3 radio buttons for Red/Green/Blue, then something like:

Dim x As Integer
With Me.lstLoadedFiles
For x = 0 To .ListCount - 1
    If .ItemData(x) = Choose(Me.optColor, "Red", "Green", "Blue") Then .Selected(x) = True
Next
End With

Access: Multi-Listbox - Select items if they equal a specific string using VBA

1 Answers