Sorting List for Combo Box Value List

  • Thread starter Thread starter Craig Ferrier
  • Start date Start date
C

Craig Ferrier

I have a field that holds titles. ie Mr Mrs Ms Dr Rev etc
These are in a value list - not associated to a table
I have the code to add any new title for the On Not In List event.
Is there a way to sort these alphabetically when next display the combo box
in the form.

Thanks
Craig
 
I believe you would need to programatically read the value list into
variables, sort the values into the desired order, and then set the
RowSource property to the resorted list. This can be done using an Array
Variable:

Dim varArray As Variant
Dim strRowSource As String
strRowSource = Me.cboBoxName.RowSource
varArray = Split(strRowSource, ";")
Call SortAnArray(varArray, True)
strRowSource = Join(varArray, ";")
Me.cboRowSource = strRowSource
Me.cboRowSource.Requery



Paste the following subroutine into a regular module:

Public Sub SortAnArray(xvarArrayName As Variant, Optional ByVal
xblnSortAscending As _
Boolean = True)
'***THIS SUBROUTINE USES A "BUBBLE SORT" PROCESS TO SORT THE VALUES STORED
IN A
'***ONE-DIMENSIONAL ARRAY IN EITHER ASCENDING OR DESCENDING ORDER. THE VALUE
OF
'***xblnSortOrder VARIABLE (TRUE = ASCENDING; FALSE = DESCENDING) DETERMINES
THE
'***SORTING ORDER.

' xblnSortAscending is boolean variable that tells the subroutine which
order to use
' when sorting the values
' xintLBound is integer variable that holds the lower bound value of the
' varArrayName variable
' xintLoop is integer variable used as a loop counter
' xintMoop is integer variable used as a loop counter
' xintUBound is integer variable that holds the upper bound value of the
' varArrayName variable
' xvarArrayName is variant array variable that holds the values to be sorted
(must
' be a one-dimensional array for this routine to work)
' xvarTemp is variant variable used to temporarily hold a value while two
values
' are being exchanged by the sorting routine

Dim xintLBound As Integer, xintUBound As Integer, xintLoop As Integer,
xintMoop As Integer
Dim xvarTemp As Variant

xintLBound = LBound(xvarArrayName)
xintUBound = UBound(xvarArrayName)

If xblnSortAscending = True Then
'sort in ascending order
For xintLoop = xintLBound To xintUBound - 1
For xintMoop = xintLoop + 1 To xintUBound
If xvarArrayName(xintLoop) > xvarArrayName(xintMoop) Then
xvarTemp = xvarArrayName(xintLoop)
xvarArrayName(xintLoop) = xvarArrayName(xintMoop)
xvarArrayName(xintMoop) = xvarTemp
End If
Next xintMoop
Next xintLoop
Else
'sort in descending order
For xintLoop = xintLBound To xintUBound - 1
For xintMoop = xintLoop + 1 To xintUBound
If xvarArrayName(xintLoop) < xvarArrayName(xintMoop) Then
xvarTemp = xvarArrayName(xintLoop)
xvarArrayName(xintLoop) = xvarArrayName(xintMoop)
xvarArrayName(xintMoop) = xvarTemp
End If
Next xintMoop
Next xintLoop
End If
End Sub
 
Back
Top