Sorting a listbox when items are added

[Ayo]

I have 2 listboxes on a userform, When I select items from one to the other
and back, the items are always showing at the bottom of the list. Is there a
way to make the sort ascending, everytime a nd item is returned to its
original listbox?
So far, I use 2 commandbuttons cmdSelect and cmdDeselect to move items
between the two listboxes. The click event codes for these buttons follows:

Private Sub cmdSelect_Click()
Dim Msg, Style, Title

Me.ListBox2.Enabled = True
For i = 0 To ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True And i <= 25 Then
Me.ListBox2.AddItem ListBox1.List(i)
ElseIf ListBox1.Selected(i) = True And i > 25 Then
Msg = "25 sites maximum is allowed" ' Define message.
Style = vbOKOnly + vbInformation + vbDefaultButton1 ' Define
buttons.
Title = "Maximum Selections Allowed" ' Define title.
Exit For
End If
Next i

For i = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(i) = True Then
ListBox1.RemoveItem i
End If
Next i
End Sub

Private Sub cmdDeselect_Click()
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) = True Then
ListBox1.AddItem ListBox2.List(i)
End If
Next i

For i = ListBox2.ListCount - 1 To 0 Step -1
If ListBox2.Selected(i) = True Then
ListBox2.RemoveItem i
End If
Next i

If ListBox2.ListCount = 0 Then
Me.cmdDeselect.Enabled = False
Me.cmdDeselectAll.Enabled = False
End If
End Sub

 

[OssieMac]

The following code is a sample to sort the values in a ListBox on a
Worksheet. However, I think that it should work on a form also if you
substitue the form details for the sheet details.

Sub SortListBox()
Dim tempVar As Variant
Dim i As Integer
Dim j As Integer
With Sheets("Sheet1").ListBox1
For j = 0 To .ListCount - 2
For i = 0 To .ListCount - 2
If .List(i) > .List(i + 1) Then
tempVar = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = tempVar
End If
Next i
Next j
End With

End Sub

 

[OssieMac]

Afterthought. If ListBox contains numerics then need to use Val function when
comparing items otherwise they sort as text and 10 will follow 1 and 20 will
follow 2 etc. The following modified example for sorting numerics was tested
on a UserForm.

Private Sub CommandButton1_Click()
'This sample on a UserForm with numeric values in the ListBox

Dim tempVar As Variant
Dim i As Integer
Dim j As Integer

With Me.ListBox1
For j = 0 To .ListCount - 2
For i = 0 To .ListCount - 2
If Val(.List(i)) > Val(.List(i + 1)) Then
tempVar = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = tempVar
End If
Next i
Next j
End With

End Sub

 

[Ayo]

Thanks OssieMac., that did the trick.

The following code is a sample to sort the values in a ListBox on a
Worksheet. However, I think that it should work on a form also if you
substitue the form details for the sheet details.

Sub SortListBox()
Dim tempVar As Variant
Dim i As Integer
Dim j As Integer
With Sheets("Sheet1").ListBox1
For j = 0 To .ListCount - 2
For i = 0 To .ListCount - 2
If .List(i) > .List(i + 1) Then
tempVar = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = tempVar
End If
Next i
Next j
End With

End Sub

 

[Patrick Molloy]

i've found that using a hidden list and populating it from the target list,
copying in the new value at the appropriate point, is often faster than
sorting when large lists are used.
Another alternative is to set the source for the list as a sheet range, and
the use the sort function in excel take care of soring - no point in
reinventing the wheel