Ken Warthen;477444 Wrote:
I have a combobox on an Excel 2007 userform to which I'd like to add
the
elements of a worksheet range in a sorted order. There may be anywhere
from
several hundred to a few thousand items. I'm able to add the items by
iterating through the named range, but users have now requested that
the
elements be displayed in alpha or numeric order. I've looked around
and
can't find any relatively simple way to do this. Any help will be
greatly
appreciated.

Ken

Hello Ken,

If you are loading the ComboBox using the RowSource property, it is
very easy to sort the data. This code is for Excel 2003. I don't have
Excel 2007 but I do know the Sort method for Range is slightly different
from Excel 2003. You need to make some adjustments. You can call this
macro from your routines to either add or remove items in the ComboBox.
Change the name of the ComboBox1 in the code to your ComboBox name.

--------------------------------
Sub SortComboBox()
Dim Rng As Range
Set Rng = Range(ComboBox1.RowSource)
Rng.Sort Key1:=Rng.Cells(1, 1), Order1:=xlAscending, _
Header:=xlNo, Orientation:=xlTopToBottom, _
MatchCase:=False
End Sub
--------------------------------


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131726