Sorting of values within a list box

Z

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
 
B

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)
 
Z

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
 
Z

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
 
B

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)
 
Z

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
 
Top