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

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/

Sorting of values within a list box
 

Harald - Thanks for the info. I'll give it a go

--
Message posted from http://www.ExcelForum.com

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/

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

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

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/

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/

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/

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