How to put values in order in a listbox ascending

[Eric Penfold]

This code takes values from a folder then puts them into a listbox . But how can sort numbers numerically

Sub ListFiles()

        Dim FSOLibary As FileSystemObject
        Dim FSOFolder As Object
        Dim FSOFile As String
        Dim FolderName As String
        Dim SourcePath As String
        Dim SubPath As String
        Dim CmbData

        If Not Me.OpenDrawing = "" Then

        CmbData = Split(Me.OpenDrawing.Value, "-")
        CmbData(0) = Replace(CmbData(0), "-", "")

        SourcePath = "\\dc01\Company\R&D\Drawing Nos"
        
    If Val(CmbData(0)) >= 10001 And Val(CmbData(0)) <= 10050 Then
        SubPath = "10001-10050"
    ElseIf Val(CmbData(0)) >= 10051 And Val(CmbData(0)) <= 10100 Then
        SubPath = "10051-10100"
    ElseIf Val(CmbData(0)) >= 10101 And Val(CmbData(0)) <= 10150 Then
        SubPath = "10101-10150"
    ElseIf Val(CmbData(0)) >= 10151 And Val(CmbData(0)) <= 10200 Then
        SubPath = "10151-10200"
    End If


        Me.PdfDrawingList.Clear

        On Error Resume Next

        FolderName = (SourcePath & "\" & SubPath & "\" & Int(CmbData(0))) & "\"
        Set FSOLibary = New Scripting.FileSystemObject
        Set FSOFolder = FSOLibary.GetFolder(FolderName)
        FSOFile = Dir(FSOFolder & "\" & "*.pdf", vbReadOnly)

         Do While FSOFile <> ""
         Me.PdfDrawingList.AddItem FSOFile
         FSOFile = Dir
         Loop
         
         End If
        
End Sub

 

[Eric Penfold]

Thanks all working a treat now

 

[John_w]

From the OP code, replace

Do While FSOFile <> ""
     Me.PdfDrawingList.AddItem FSOFile
     FSOFile = Dir
Loop

with

With Me.PdfDrawingList
    Do While FSOFile <> ""
        For i = 0 to .ListCount - 1
            If FSOFile < .List(i) Then
                .AddItem FSOFile, i
                Goto NextLoop
            End If
        Next i
        .AddItem FSOFile
NextLoop:
         FSOFile = Dir
     Loop
End With

Thanks Mike - your solution is much simpler! I went down a 'think array sort' rabbit hole when it's easier to just insert the items in the correct position.

If you change If FSOFile < .List(i) Then
to If StrCmpLogicalW(StrConv(FSOfile, vbUnicode), StrConv(.List(i), vbUnicode)) = -1 Then
then the file names are inserted in numerical order.