Word 2000: Sort a Combo Box

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
Thu Dec 04 09:51:05 CST 2003

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
Thu Dec 04 11:38:08 CST 2003

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.


"Helmut Weber" <elmkqznfwvccbf@mailinator.com> wrote in message
news:0ac001c3ba7e$6cf6dff0$a501280a@phx.gbl...
> 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
Thu Dec 04 11:54:55 CST 2003

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.


"VBA Coder" <noone@account.com.NO_SPAM.> wrote in message
news:%23l0tj1ouDHA.2340@TK2MSFTNGP12.phx.gbl...
> 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.
>
>
> "Helmut Weber" <elmkqznfwvccbf@mailinator.com> wrote in message
> news:0ac001c3ba7e$6cf6dff0$a501280a@phx.gbl...
> > 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
>
>

Lars-Eric
Thu Dec 04 16:28:13 CST 2003

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.

--
Regards,
Lars-Eric Gisslén

"VBA Coder" <noone@account.com.NO_SPAM.> skrev i meddelandet
news:O8jN8%23ouDHA.1224@TK2MSFTNGP09.phx.gbl...
> 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.
>
>
> "VBA Coder" <noone@account.com.NO_SPAM.> wrote in message
> news:%23l0tj1ouDHA.2340@TK2MSFTNGP12.phx.gbl...
> > 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.
> >
> >
> > "Helmut Weber" <elmkqznfwvccbf@mailinator.com> wrote in message
> > news:0ac001c3ba7e$6cf6dff0$a501280a@phx.gbl...
> > > 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
> >
> >
>
>

Helmut
Fri Dec 05 12:04:21 CST 2003

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
Mon Dec 08 09:48:43 CST 2003

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


"Helmut Weber" <elmkqznfwvccbf@mailinator.com> wrote in message
news:021001c3bb5a$35bc75e0$a101280a@phx.gbl...
> 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
>

Helmut
Tue Dec 09 10:07:13 CST 2003

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

VBA
Fri Dec 12 14:43:15 CST 2003

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.

"Helmut Weber" <elmkqznfwvccbf@mailinator.com> wrote in message
news:0ac001c3ba7e$6cf6dff0$a501280a@phx.gbl...
> 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
Fri Dec 12 15:09:56 CST 2003

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


"VBA Coder" <noone@account.com.NO_SPAM.> wrote in message
news:%238Q6SCPwDHA.1764@TK2MSFTNGP10.phx.gbl...
> 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.
>
> "Helmut Weber" <elmkqznfwvccbf@mailinator.com> wrote in message
> news:0ac001c3ba7e$6cf6dff0$a501280a@phx.gbl...
> > 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
>
>

Word
Fri Dec 12 16:21:59 CST 2003

G'day "VBA Coder" <noone@account.com.NO_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




"VBA Coder" <noone@account.com.NO_SPAM.> was spinning this yarn:

>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.
>
>"Helmut Weber" <elmkqznfwvccbf@mailinator.com> wrote in message
>news:0ac001c3ba7e$6cf6dff0$a501280a@phx.gbl...
>> 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
>

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.