| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting combobox elements
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting combobox elements
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) On Fri, 4 Sep 2009 10:45:02 -0700, Ken Warthen 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting combobox elements
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting combobox elements
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 "Chip Pearson" wrote: 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) On Fri, 4 Sep 2009 10:45:02 -0700, Ken Warthen 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting combobox elements
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
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"
QSortInPlace (ReportArray)
frmReport.ltbReport.List() = ReportArray
frmReport.Show
End If
End If
End Sub
"Chip Pearson" wrote: 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) On Fri, 4 Sep 2009 10:45:02 -0700, Ken Warthen 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sorting items in a combobox | Excel Programming | |||
| Combobox - Sorting Data | Excel Programming | |||
| Sorting dates in a combobox | Excel Programming | |||
| Sorting Items in a ComboBox? | Excel Programming | |||
| Sorting in a ComboBox | Excel Programming | |||
![Ken Warthen[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
