How to put values in order in a listbox ascending

Eric Penfold

Active Member
Joined
Nov 19, 2021
Messages
417
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
This code takes values from a folder then puts them into a listbox . But how can sort numbers numerically

VBA Code:
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
From the OP code, replace
VBA Code:
Do While FSOFile <> ""
     Me.PdfDrawingList.AddItem FSOFile
     FSOFile = Dir
Loop

with
VBA Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top