Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of values within a list box
I am building a reporting tool containing a form with two list boxes
these list boxes essesentially signify include or exclude. The use moves numeric values between these list boxes to choose what question will be displayed in graphs in later steps. What the user moves from one box to another can mean that the content of the list box fall out of numerical order and this can be confusin for the user (So I'm told). Is there a way to sort the values within list box, probably when an item is added or removed so that the maintain chronological order -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of values within a list box
See if
http://www.dicks-blog.com/excel/2004...g_a_multi.html is of help. HTH. Best wishes Harald "Zippy " skrev i melding ... I am building a reporting tool containing a form with two list boxes, these list boxes essesentially signify include or exclude. The user moves numeric values between these list boxes to choose what questions will be displayed in graphs in later steps. What the user moves from one box to another can mean that the contents of the list box fall out of numerical order and this can be confusing for the user (So I'm told). Is there a way to sort the values within a list box, probably when an item is added or removed so that they maintain chronological order. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of values within a list box
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of values within a list box
You can run this code on the move
Dim i As Long Dim j As Long Dim tmp With Me.ListBox1 For i = 1 To .ListCount - 1 For j = i + 1 To .ListCount - 1 If .List(i) .List(j) Then tmp = .List(i) .List(i) = .List(j) .List(j) = tmp End If Next j Next i End With -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Zippy " wrote in message ... I am building a reporting tool containing a form with two list boxes, these list boxes essesentially signify include or exclude. The user moves numeric values between these list boxes to choose what questions will be displayed in graphs in later steps. What the user moves from one box to another can mean that the contents of the list box fall out of numerical order and this can be confusing for the user (So I'm told). Is there a way to sort the values within a list box, probably when an item is added or removed so that they maintain chronological order. --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of values within a list box
Bob,
Thanks, much appreciated. I had to change "" to "<" and "i = 0 to instead of "i = 1 to" and it works. However, the numbers being sorted range from 1 to 16 and when sorte the order is 1,10,11,12,13,14,15,16,2,3,4,5 etc etc. Is there an eas way to change this to 1,2,3,4 etc. If not I can populate it wit 01,02,03 and then trim off the "0" later which I think will get aroun the problem. Thanks for your hel -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of values within a list box
Bob - I went with the 01,02,03 option and it works a treat. Thanks t
you and harald. And God bless the internet -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of values within a list box
Hi Zippy,
As long as the numbers do not execeed 99, this should work Dim i As Long Dim j As Long Dim tmp Dim fSorted As Boolean With Me.ListBox1 For i = 0 To .ListCount - 1 For j = i + 1 To .ListCount - 1 If Left("00", 3 - Len(.List(i))) & .List(i) _ Left("00", 3 - Len(.List(j))) & .List(j) Then tmp = .List(i) .List(i) = .List(j) .List(j) = tmp End If Next j Next i End With -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Zippy " wrote in message ... Bob, Thanks, much appreciated. I had to change "" to "<" and "i = 0 to" instead of "i = 1 to" and it works. However, the numbers being sorted range from 1 to 16 and when sorted the order is 1,10,11,12,13,14,15,16,2,3,4,5 etc etc. Is there an easy way to change this to 1,2,3,4 etc. If not I can populate it with 01,02,03 and then trim off the "0" later which I think will get around the problem. Thanks for your help --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of values within a list box
Why not keep them in order by inserting the addition in the proper location?
-- Regards, Tom Ogilvy "Zippy " wrote in message ... I am building a reporting tool containing a form with two list boxes, these list boxes essesentially signify include or exclude. The user moves numeric values between these list boxes to choose what questions will be displayed in graphs in later steps. What the user moves from one box to another can mean that the contents of the list box fall out of numerical order and this can be confusing for the user (So I'm told). Is there a way to sort the values within a list box, probably when an item is added or removed so that they maintain chronological order. --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of values within a list box
Compare as numbers rather than strings.
If clng(.List(i)) clng(.List(j)) Then -- Regards, Tom Ogilvy "Zippy " wrote in message ... Bob, Thanks, much appreciated. I had to change "" to "<" and "i = 0 to" instead of "i = 1 to" and it works. However, the numbers being sorted range from 1 to 16 and when sorted the order is 1,10,11,12,13,14,15,16,2,3,4,5 etc etc. Is there an easy way to change this to 1,2,3,4 etc. If not I can populate it with 01,02,03 and then trim off the "0" later which I think will get around the problem. Thanks for your help --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting of values within a list box
Tom - Nice touch. Would have been the icing on the cake except for on
of the question scores is the overall Grand mean - "GM" which makes th thing fall over. I'm really happy with the way it looks now so wil call it a day on this one. Thanks all for your help. Zipp -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Values Without Sorting Formulas | Excel Discussion (Misc queries) | |||
Identify & List unique values from a list using functions/formulas | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |