Sorting combobox elements

  • Thread starter Thread starter Ken Warthen
  • Start date Start date
K

Ken Warthen

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
 
I have a page on my web site regarding sorting arrays. See
http://www.cpearson.com/Excel/SortingArrays.aspx . On that page is a
function named QSortInPlace that will sort an array in either
ascending or descending order. The code is too lengthy to post here.
You can either copy the code off the web page or download the bas
module file from http://www.cpearson.com/Zips/modQSortInPlace.zip . If
you download the zip file, save it somewhere, unzip it, and then with
your workbook open, go to VBA, then to the File menu, choose Import
File, and open the unzipped bas file named modQSortInPlace.bas. This
will create a new module in your workbook project.

Documentation is on the web page and also within the bas file.

Once you have the code, you can put the elements in the combobox into
an array, call QSortInPlace to sort the array in either ascending or
descending order, and then reload the array back into the combobox. In
the code, change "ComboBox1" to the name of your combobox.


Dim Arr() As String
Dim N As Long
With Me.ComboBox1
ReDim Arr(0 To .ListCount - 1)
For N = 0 To .ListCount - 1
Arr(N) = .List(N)
Next N
QSortInPlace InputArray:=Arr, _
Descending:=False, CompareMode:=vbTextCompare
.Clear
.List = Arr
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Chip,

Thanks for the help. This is exactly what I was looking for, and I'm sure
I'll use your solution extensively in the future.

Ken
 
Thread necromancy. (bare with me please):

Chip, Mates, other internet companions, I appreciate your work here.

I understand a few things bout Arrays, I understand a few things about
sorting methods binary, bubble etc.

Ive been trying to get the Qsort function to work in my code.

The Function declaration is as follows:

Code: 
Public Function QSortInPlace( _
ByRef ReportArray As Variant, _
Optional ByVal LB As Long = -1&, _
Optional ByVal UB As Long = -1&, _
Optional ByVal Descending As Boolean = True, _
Optional ByVal NoAlerts As Boolean = False) As Boolean
End Function

I call the function in the following sub:

Code: 
Private Sub cmdReport_Click()
Dim sngPerc As Single 'used to calculate percentage being 100 over total
save figure
Dim strReport As String
Dim ReportArray(1 To 5) As String
Dim strError As String 'Divide by zero error check string
Dim arrReportString As String 'Sorted array for output

strReport = MsgBox("Would you like to create the total sales to date
report?", vbYesNo, "Report")
If strReport = vbYes Then
If Range("C12").Value = 0 Then
strError = MsgBox("Enter Values First", vbOKOnly)
Else
sngPerc = 100 / Range("C12").Value
ReportArray(1) = Range("C7").Value * sngPerc & "% are Daisies"
ReportArray(2) = Range("C8").Value * sngPerc & "% are Roses"
ReportArray(3) = Range("C9").Value * sngPerc & "% are Basic
Bouchets"
ReportArray(4) = Range("C10").Value * sngPerc & "% are Betty's
Beauties"
ReportArray(5) = Range("C11").Value * sngPerc & "% are Fiona's
Fantastics"
[b]QSortInPlace (ReportArray)[/b]
frmReport.ltbReport.List() = ReportArray
frmReport.Show
End If
End If
End Sub
 
Specifically what problem are you having? Details count. Also,
If strReport = vbYes Then
Click to expand...

With this line of code, you don't want strReport to be a String. It
should be a Long or, better, a VbMsgBoxResult. vbYes is a numeric
constant with a value of 6, not the word "yes".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Fri, 19 Mar 2010 07:23:01 -0700, Chip Fan <Chip
Thread necromancy. (bare with me please):

Chip, Mates, other internet companions, I appreciate your work here.

I understand a few things bout Arrays, I understand a few things about
sorting methods binary, bubble etc.

Ive been trying to get the Qsort function to work in my code.

The Function declaration is as follows:

Code: 
Public Function QSortInPlace( _
ByRef ReportArray As Variant, _
Optional ByVal LB As Long = -1&, _
Optional ByVal UB As Long = -1&, _
Optional ByVal Descending As Boolean = True, _
Optional ByVal NoAlerts As Boolean = False) As Boolean
End Function

I call the function in the following sub:

Code: 
Private Sub cmdReport_Click()
Dim sngPerc As Single 'used to calculate percentage being 100 over total
save figure
Dim strReport As String
Dim ReportArray(1 To 5) As String
Dim strError As String 'Divide by zero error check string
Dim arrReportString As String 'Sorted array for output

strReport = MsgBox("Would you like to create the total sales to date
report?", vbYesNo, "Report")
If strReport = vbYes Then
If Range("C12").Value = 0 Then
strError = MsgBox("Enter Values First", vbOKOnly)
Else
sngPerc = 100 / Range("C12").Value
ReportArray(1) = Range("C7").Value * sngPerc & "% are Daisies"
ReportArray(2) = Range("C8").Value * sngPerc & "% are Roses"
ReportArray(3) = Range("C9").Value * sngPerc & "% are Basic
Bouchets"
ReportArray(4) = Range("C10").Value * sngPerc & "% are Betty's
Beauties"
ReportArray(5) = Range("C11").Value * sngPerc & "% are Fiona's
Fantastics"
[b]QSortInPlace (ReportArray)[/b]
frmReport.ltbReport.List() = ReportArray
frmReport.Show
End If
End If
End Sub



Chip Pearson said:
I have a page on my web site regarding sorting arrays. See
http://www.cpearson.com/Excel/SortingArrays.aspx . On that page is a
function named QSortInPlace that will sort an array in either
ascending or descending order. The code is too lengthy to post here.
You can either copy the code off the web page or download the bas
module file from http://www.cpearson.com/Zips/modQSortInPlace.zip . If
you download the zip file, save it somewhere, unzip it, and then with
your workbook open, go to VBA, then to the File menu, choose Import
File, and open the unzipped bas file named modQSortInPlace.bas. This
will create a new module in your workbook project.

Documentation is on the web page and also within the bas file.

Once you have the code, you can put the elements in the combobox into
an array, call QSortInPlace to sort the array in either ascending or
descending order, and then reload the array back into the combobox. In
the code, change "ComboBox1" to the name of your combobox.


Dim Arr() As String
Dim N As Long
With Me.ComboBox1
ReDim Arr(0 To .ListCount - 1)
For N = 0 To .ListCount - 1
Arr(N) = .List(N)
Next N
QSortInPlace InputArray:=Arr, _
Descending:=False, CompareMode:=vbTextCompare
.Clear
.List = Arr
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
Click to expand...
Click to expand...
 
Back
Top