Word 2000: Sort a Combo Box

[VBA Coder]

Does anyone know how I can sort an unbound combo box using Microsoft Word
and VBA? In my Initialize function of my form, I am using the .AddItem
method to populate my combo box. The user has the ability to add new items
to the combo box. I would like to be able to sort the combo box right after
the user adds a new item to the combo box. Does anyone have any code or
ideas of how I can do this? Would the same apply for a List Box? Because I
will eventually add this functionality later, but the Combo box is 1st
priority.

Thanks.

 

[Helmut Weber]

Hi, try this:
Private Sub ComboBox1_Change()
Dim k As Integer
Dim i As Integer
Dim j As Integer
Dim aBuf As String
With ComboBox1
k = .ListCount
For j = 0 To k - 1
For i = (j + 1) To (k - 1)
If .List(i) < .List(j) Then
aBuf = .List(j)
.List(j) = .List(i)
.List(i) = aBuf
End If
Next i
Next j
Jt = .List(k - 1)
End With
End Sub
Note: This sorts alphanumerically, e g.
1
11
2
Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr$(64) & "t-online.de"
Word 97, W98

 

[VBA Coder]

A syntax error occurs on your last line just before the "End With", at the
line "Jt = .List(k - 1)". What is Jt? It is not defined as a variable
anywhere.

 

[VBA Coder]

When I changed it to aBuf, it all worked.

I did add extra code that will reselect the value that the user entered,
because the way you have it coded, after it sorts, the last item in the
combo box is selected.

Other than that, it worked like a charm. Thank you very much.

 

[Lars-Eric Gisslén]

VBA Coder,

The funny thing is that manual sorting should not be neccesary at all. The
ComboBox is actually three controls, an edit control, button and a ListBox.
The ComboBox is a standard Windows Control and the ListBox/ComboBox has a
sort style (CBS_SORT/LBS_SORT) that will make Windows sort the content in
control when the style is set. For some reason it's left out in VBA.

 

[Helmut Weber]

Hi,
you may delete the line that contains "ft".
It's a remainder from the programm,
from which I cut this routine out.
It does nothing in this context.
Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr$(64) & "t-online.de"
Word 97, W98

 

[VBA Coder]

Don't you mean the line, "Jt = .List(k - 1)", as there is no line with "ft"

 

[Helmut Weber]

Hi,
of course. Typing errors and typing errors...
Not my day.
Helmut Weber

 

[VBA Coder]

Helmut,

You code was a winner, but now the client wants the ability to sort
Descending. I have been playing around with your code, but can't quite get
it to sort Descending.

Can you help me out?

Thanks.

 

[VBA Coder]

Got it to work - I changed your "<" to ">" and it sorted in Descending order

 

[Word Heretic]

G'day "VBA Coder" <[email protected]_SPAM.>,

FWIW, WordBasic.Sort MyArray is MUCH faster

Additionally, there are 3 different sort techniques for 3 different
situations.

Bubble sort: Small lists (<100 items, ie all our userform
requirements) and almost-sorted lists.

Shell sort: 100-1000 items, maybe more dep on instancing.

Quick sort/Partition Sort: 1000 items plus

To round up the whole subject, there is also the batch sort. You use
this for extremely large databases where you cannot load the entire
dataset into memory. As it is the least documented and most useful to
us (the other 3 are better off being replaced with
wordbasic.sortarray) I include some pseudo-code for the reader's
erudition.

open outputfile
open inputfile

'Precursor
'A simple way to make runs out of existing almost sorted data

While not eof(inputfile)
read next value
if value > oldvalue then
write to outputfile
else
close outputfile
open outputfilename+1
end if
wend

'converge runs

lastoutputfilename =outputfile

while outputfilename < lastoutputfilename
open oldoutputfilename for read
open oldoutputfilename +1 for read
open newoutputfilename for append (use the same naming convention,
num sequencing as last output file series, we just continue the
series)
read var1 from oldoutputfilename
read var2 from oldoutputfilename+1

if var1<var2

write var1
read next var1
if eof oldoutputfilename then write remainder of other file to
output

else

write var 2
read next var2
if eof oldoutputfilename+1 then write remainder of other file to
output

end if

close & del both read files

lastoutputfilename=newoutputfilename

inc oldoutputfile +2
wend
rename last output file to desired name, it contains the sorted
output.

Depending on the sort characteristics of the data itself and memory
available, you can also load in buffers worth of data and sort that
into a run for dumping to an outputfile as the precursor processing.

This is quite common in batch processing as you redo the process over
numerous data sources to obtain your input figures, and generally you
sort from each data source before output. Your routine then runs the
converge, etc.

ps: change

If .List(i) < .List(j) Then

to a > to get descending

Helmut,

You code was a winner, but now the client wants the ability to sort
Descending. I have been playing around with your code, but can't quite get
it to sort Descending.

Can you help me out?

Thanks.

Steve Hudson

Word Heretic, Sydney, Australia
Tricky stuff with Word or words for you.
wordheretic.com

If my answers r 2 terse, ask again or hassle an MVP,
at least they get recognition for it then.
Lengthy replies offlist require payment.