sorting a column in a listBox when the value of the column is an integer

I needed to sort by ID (an integer) in a listBox.
I had a lot of trouble getting this working - used the CompareRows function and it still sorted as a String .
I realised that the function expected a text value, then converted the value in order to do the comparison. So - here are the changes I made to the case where column 0 (1st column is an integer, rather than a number stored as a text value.

Function CompareRows(row1 As Integer, row2 As Integer, column As Integer, ByRef result As Integer) As Boolean
Select Case column
Case 0 // This is an integer column, not a text value which is a number. In order to prepare it as the function requires, need to convert to text, then take the value

 If Val(Me.Cell(row1, column ).ToText) < Val(Me.Cell(row2, column).ToText) Then
  result = -1
ElseIf Val(Me.Cell(row1, column).ToText) > Val(Me.Cell(row2, column).ToText) Then
  result = 1
Else
  result = 0
End If
Return True

Case 1 // This is our numerical value column. Let’s do the work ourselves
Function CompareRows(row1 As Integer, row2 As Integer, column As Integer, ByRef result As Integer) As Boolean
Select Case column
Case 0 // This is a string column. Let the listbox manage it by returning false
Return False

Case 1 // This is our numerical value column. Let’s do the work ourselves
If Val(Me.Cell(row1, column )) < Val(Me.Cell(row2, column)) Then
result = -1
ElseIf Val(Me.Cell(row1, column)) > Val(Me.Cell(row2, column)) Then
result = 1
Else
result = 0
End If
Return True

Else //some other column for which we let the listbox handle comparison
Return False
End Select
End Function

Else //some other column for which we let the listbox handle comparison
Return False
End Select
End Function

Just FYI, instead of all those if then else statements, you could also do this:

Result = Sign(Val(Me.Cell(row1, column )) - Val(Me.Cell(row2, column))) Return True

Another way when the ID’s are inserted into the cells is to format them padded with leading zeros, then the listbox will sort in the order desired eg…

myListbox.cell(row,0)=format(ID, formatString)

For n rows, the number of zeros required in formatString is 1+log(n) / log(10).