Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort an array to use in a listbox on a userform in Excel 2007
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort an array to use in a listbox on a userform in Excel 2007
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" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort an array to use in a listbox on a userform in Excel 2007
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? On 22 jun, 05:51, Joel wrote: 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" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort an array to use in a listbox on a userform in Excel 2007
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" wrote: 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? On 22 jun, 05:51, Joel wrote: 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" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort an array to use in a listbox on a userform in Excel 2007
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort an array to use in a listbox on a userform in Excel 2007
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. "Renate" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listbox on Excel Userform | Excel Programming | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
Sort a multi column listbox in userform | Excel Programming | |||
How to sort ListBox or Array? | Excel Programming |