VBA Access - alphabetically sort listbox

Question

I have two listboxes: listbox1 and listbox2. Listbox2 is a value list that is populated via user selection. I need to prevent duplicates from being entered into listbox2. My current solution checks the previous listbox record to see if it equals the current loop iteration- and if so, deletes the duplicate:

Set ctlSource = Me!listbox1
For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
        strItems = "'" & ctlSource.Column(1, intCurrentRow) & "'"
        Me!listbox2.AddItem (strItems)
    End If
Next intCurrentRow

Dim intItems As Integer
Dim i As Integer



'deletes  duplicates
For i = 0 To Me.listbox2.ListCount - 1
        If Me!listbox2.ItemData(i) = Me!listbox2.ItemData(i - 1) Then
             Me!listbox2.RemoveItem (i)
        End If
Next i

However, this requires that listbox2 be sorted. I want listbox2 to be sorted alphabetically anyway, so this method makes the most sense to me. I'm somewhat flabbergasted that I can't seem to find an existing listbox method or function that allows me to do this easily. I've perused through this forum and others, but haven't found a good solution. I guess R, Python, and SQL spoiled me. Any help would be appreciated...

it's perhaps easiest to put the valid entries from listbox1 into an array first, then sort the array, and then loop through it to add the items to the listbox2. You should be able to do this after deleting duplicates. Instead of removing the item, just add those that don't match to an array, sort the array and then load the array values into the listbox2. — mooseman
since the list items are likely in a worksheet, use the auto filter unique or sort functions on the range to eliminate duplicates and sort however you like "before" you load the list. I am not understanding I guess, you have two list boxes to delete the duplicates from list 2 that exist in list 1? I recommend before the list is refreshed. — Wookies-Will-Code

TinMan TinMan · Accepted Answer · 2018-06-26T21:32:54

Using a Dictionary or collection will make it easy to check for duplicates. 'ArrayList' and 'SortedList' have .Sort methods that will sort the data for you. I recommend using an ActiveX listbox if you want to add the data manually.

This GIF uses the "ArrayList Demo Code" to demonstrate how to use an ArrayList to remove duplicates and sort values.

ArrayList Demo Code

Private Sub btnAddItem_Click()
    Dim n As Long
    Dim lbox As MSForms.ListBox, list As Object
    Set lbox = Me.ListBox0.Object
    Set list = CreateObject("System.Collections.ArrayList")

    For n = Me.ListBox2.ListCount - 1 To 0 Step -1
        If Not list.Contains(Me.ListBox2.ItemData(n)) Then list.Add Me.ListBox2.ItemData(n)
        Me.ListBox2.RemoveItem n
    Next

    If Not list.Contains(Me.Text6.Value) Then list.Add Me.Text6.Value

    list.Sort

    For n = 0 To list.Count - 1
        Me.ListBox2.AddItem list(n)
    Next

    lbox.list = list.ToArray
End Sub

Private Sub btnFillList_Click()
    Dim lbox As MSForms.ListBox
    Set lbox = Me.ListBox0.Object
    Dim n As Long
    For n = 65 To (65 + 25) Step 2
        lbox.AddItem Chr(n)
        Me.ListBox2.AddItem Chr(n)
    Next
End Sub

Sub btnRemoveAll_Click()
    Dim n As Long
    Dim lbox As MSForms.ListBox
    Set lbox = Me.ListBox0.Object
    For n = Me.ListBox2.ListCount - 1 To 0 Step -1
        Me.ListBox2.RemoveItem n
    Next
    lbox.clear
End Sub

OP Comment: "I guess R, Python, and SQL spoiled me"

MS Access is a database. The MS Access controls can be linked directly to Recordsets, Queries and Tables. You could simple create a Table to hold the values with a value field that does not accept duplicates. Then link the ListBox directly to a Query that returns the sorted values from the Table. Easy Peasy!

VBA Access - alphabetically sort listbox

2 Answers

ArrayList Demo Code

OP Comment: "I guess R, Python, and SQL spoiled me"