Sort ListBox elements by size

Question

I have a system in my UserForm with 2 ListBoxes. One on the left and one on the right. I've done the function so that I can pass any element back and forth between the 2 ListBoxes. I wanted then to sort the elements (strings) in alphabetical order AND by size. What I mean by size is that a string with 10 char will be displayed after a string of 9 char.

Here's what I've tried :

Public Sub Sort_Listboxes(ByVal ListBox As msforms.ListBox)
Dim i As Long
Dim j As Long
Dim Temp As Variant

For i = 0 To ListBox.ListCount - 2
    For j = i + 1 To ListBox.ListCount - 1
        If ListBox.List(i) > ListBox.List(j) Or Len(ListBox.List(i)) > Len(ListBox.List(j)) Then
            Temp = ListBox.List(j)
            ListBox.List(j) = ListBox.List(i)
            ListBox.List(i) = Temp
        End If
    Next j
Next i

End Sub

With this function I get this result :

Element_1
Element_10
Element_2
Element_3

It seems normal if we take only the first part of the condition but with that second part it should work...

Note : I call the Sort_ListBoxes function for both list in both of my functions click_right and click_left

Answer 1

If the correct order that you are after is

Element_1
Element_2
Element_3
Element_10

then try replacing the If line with

If Len(ListBox.List(i)) > Len(ListBox.List(j)) Or (Len(ListBox.List(i)) = Len(ListBox.List(j)) And ListBox.List(i) > ListBox.List(j)) Then

... just amends the logic slightly to correctly handle strings with the same length.