Sorting of values within a list box

[Zippy]

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

 

[Harald Staff]

See if
http://www.dicks-blog.com/excel/2004/05/sorting_a_multi.html
is of help.

HTH. Best wishes Harald

 

[Zippy]

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

 

[Bob Phillips]

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]

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

 

[Zippy]

Bob - I went with the 01,02,03 option and it works a treat. Thanks t
you and harald. And God bless the internet

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

Why not keep them in order by inserting the addition in the proper location?

 

[Tom Ogilvy]

Compare as numbers rather than strings.

If clng(.List(i)) > clng(.List(j)) Then

 

[Zippy]

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