Sort an array to use in a listbox on a userform in Excel 2007

[Renate]

Hi guys,

Please bear with me, I can't get this sorting the way I need it to
work. I've serached on the archives, but somehow I couldn't find an
example that works for my array.

The array contains filenames including the path etc and some sort of
description which I create from leaving parts out of the filename,
for example:

sFiles(0,0) = "c:\AB 123.xls"
sFiles(1,0) = "123"
sFiles(0,1) = "c:\AB 234.xls"
sFiles(1,1) = "234"
sFiles(0,2) = "c:\AB 898.xlam"
sFiles(1,2) = "898"
sFiles(0,2) = "c:\999.doc"
sFiles(1,2) = "999"


I use this data in a listbox with two columns. Only one of the columns
is visible to the user. I want to create an option on the userform to
sort the listbox on either the fullname or on the description.

How can I archieve this?

 

[Joel]

Sub sortarray()

Dim sfiles(8, 2) '<= this line is optional depending how your array is
defined

SortRow = 0 'could be 0 or 1

For i = 0 To (UBound(sfiles) - 1)
For j = 1 To UBound(sfiles)
If sfiles(i, SortRow) > sfiles(j, SortRow) Then
'swap data items
temp = sfiles(i, 0)
sfiles(i, 0) = sfiles(j, 0)
sfiles(j, 0) = temp

temp = sfiles(i, 1)
sfiles(i, 1) = sfiles(j, 1)
sfiles(j, 1) = temp
End If
Next j
Next i

End Sub

 

[Renate]

Hi Joel,

Thanks for your post. I've used your code inside mine and the result
when looping lbound(sfiles,2) to ubound(sfiles,2) is

123 : c:\AB 123.xls
234 : c:\AB 234.xls
c:\999.doc : 999

What I would like to have as a result is:

when sorting on the second column:
c:\AB 123.xls 123
c:\AB 234.xls 234
c:\999.doc 999

when sorting on the first column:
c:\999.doc 999
c:\AB 123.xls 123
c:\AB 234.xls 234

Any thoughts on how to change your code to get this result?

 

[Joel]

first, make the following change in my code to get it to run in less steps.

from
For j = 1 To UBound(sfiles)

to
For j = (i + 1) To UBound(sfiles)


I don't understand you last question. Why don't you always sort on the last
column?

Do you want to extract the number from the full string?

MyString = "c:\AB 123.xls"
'remove folders
Do While InStr(MyString, "\") > 0
MyString = Mid(MyString, InStr(MyString, " ") + 1)
Loop

'check for blanks'
Do While InStr(MyString, " ") > 0
MyString = Mid(MyString, InStr(MyString, " ") + 1)
Loop
'remove extension
If InStr(MyString, ".") > 0 Then
MyString = Left(MyString, InStr(MyString, ".") - 1)
End If

 

[Renate]

Hi Joel,

I don't understand you last question. Why don't you always sort on the last
column?

Maybe I didn't explain it well enough. The real listbox get's filled
with all filenames in a specific folder. Each filename has a code
before it, it's a two letter and four digit code.
This code is followed by a description.
By default the listbox showes the names including the codes.
I want to add a checkbox on my form, that when the user selects it,
shows only the description in the listbox and sorts it ascending.

For example:
listbox sorted ascending with full filename
ab 1234 Description B
ab 6889 Description F
ab 8990 Description A

If I want only to show the descriptions and sort accordingly, it would
be:
Description A
Description B
Description F

The array contains the full filename in the first column and the
description in the second. I use that to fill the listbox.
Does this make more sense?

 

[Joel]

I think the confusing is over the term "Column". I sometimes refer to an
array consisting of rows and columns. You were refering to the data as
columns.

You can make your array wider and add the base filename into another column
in your array.

Description Full Path Name Base filename


Use my code from the last posting to extract the Base filename from the Full
path. You would need to modify the sort code to swap 3 indexes in stead of
the two presently being swapped

Present : <=> indicates swap during the sort
sfiles(i, 0) <=> sfiles(j, 0) <=Desription
sfiles(i, 1) <=> sfiles(j, 1) <= Full Path Name


change
sfiles(i, 0) <=> sfiles(j, 0) <= Description
sfiles(i, 1) <=> sfiles(j, 1) <= Full Path Name
sfiles(i, 2) <=> sfiles(j, 2) <= Base Filename

You could actually put all the data in the Listbox and just hide the unused
columns instead of using a seperate array.