Author Topic: How to Sort a ComboBox?  (Read 3583 times)

0 Members and 1 Guest are viewing this topic.

Humbertogo

  • Guest
How to Sort a ComboBox?
« on: September 05, 2007, 07:50:30 AM »
Is there a way to sort comboboxes alphabetically

hendie

  • Guest
Re: How to Sort a ComboBox?
« Reply #1 on: September 05, 2007, 08:02:20 AM »
author unknown

Code: [Select]
Public Function CBsort(CB As ComboBox)
' Sort the Combobox
    Dim CBvar As Variant
    For I = 0 To CB.ListCount - 2
   
        If CB.List(I) > CB.List(I + 1) Then
            CBvar = CB.List(I)
            CB.List(I) = CB.List(I + 1)
            CB.List(I + 1) = CBvar
            I = -1
        End If
    Next I
End Function

Humbertogo

  • Guest
Re: How to Sort a ComboBox?
« Reply #2 on: September 05, 2007, 08:19:50 AM »
Thanks for the code.
only this take a long time to sort a list with in 90.00000 items :-(

Guest

  • Guest
Re: How to Sort a ComboBox?
« Reply #3 on: September 05, 2007, 08:24:29 AM »
Sort the information PRIOR to adding it to the combobox.  What's your code look like?  Can you post a snippet?

hendie

  • Guest
Re: How to Sort a ComboBox?
« Reply #4 on: September 05, 2007, 08:32:03 AM »
it might be easier/faster to read the items into an array, sort the array then add the array to the combobox

Kerry

  • Mesozoic relic
  • Seagull
  • Posts: 11654
  • class keyThumper<T>:ILazy<T>
Re: How to Sort a ComboBox?
« Reply #5 on: September 05, 2007, 09:22:35 AM »
Thanks for the code.
only this take a long time to sort a list with in 90.00000 items :-(

How many ??
kdub, kdub_nz in other timelines.
Perfection is not optional.
Everything will work just as you expect it to, unless your expectations are incorrect.
Discipline: None at all.

Bryco

  • Water Moccasin
  • Posts: 1882
Re: How to Sort a ComboBox?
« Reply #6 on: September 05, 2007, 09:40:32 AM »
http://vbnet.mvps.org/index.html?code/sort/qsoverview.htm
QuickSort may be the one, but it will take a few seconds for sure

hendie

  • Guest
Re: How to Sort a ComboBox?
« Reply #7 on: September 05, 2007, 10:06:48 AM »
Thanks for the code.
only this take a long time to sort a list with in 90.00000 items :-(

How many ??

exactly !

why would you want/need to add 9000000 items to a combo box ??????

Keith™

  • Villiage Idiot
  • Seagull
  • Posts: 16899
  • Superior Stupidity at its best
Re: How to Sort a ComboBox?
« Reply #8 on: September 05, 2007, 01:25:51 PM »
The simple solution is to set the Sorted property to True in the IDE at designtime.

At runtime a ComboBox Sorted property is read only, thus you need to set it prior to running the code.
Proud provider of opinion and arrogance since November 22, 2003 at 09:35:31 am
CadJockey Militia Field Marshal

Find me on https://parler.com @kblackie

Guest

  • Guest
Re: How to Sort a ComboBox?
« Reply #9 on: September 05, 2007, 01:30:31 PM »
The simple solution is to set the Sorted property to True in the IDE at designtime.

At runtime a ComboBox Sorted property is read only, thus you need to set it prior to running the code.

But a ComboBox in VBA doesn't have an option for SORTED. (unless it's called something else??)

Keith™

  • Villiage Idiot
  • Seagull
  • Posts: 16899
  • Superior Stupidity at its best
Re: How to Sort a ComboBox?
« Reply #10 on: September 05, 2007, 01:42:30 PM »
Hmmmm .. I have been working in VB recently .. hadn't considered the fact the properties would be different in VB and VBA ...

let me see what I can come up with
Proud provider of opinion and arrogance since November 22, 2003 at 09:35:31 am
CadJockey Militia Field Marshal

Find me on https://parler.com @kblackie

Humbertogo

  • Guest
Re: How to Sort a ComboBox?
« Reply #11 on: September 10, 2007, 09:59:09 AM »
thanks

Code: [Select]

  'fill MyArray
  For i = 1 To UBound(ComboBox1.List)
    MyArray(i) = ComboBox1.List(i)
  Next i

QuickSort MyArray, LBound(MyArray), UBound(MyArray)


  ComboBox1.Clear
   'use sorted array to refill ComboBox
  For i = 1 To UBound(MyArray)
  If MyArray(i) <> "" Then _
    ComboBox1.AddItem MyArray(i)
  Next i

Code: [Select]

Private Sub QuickSort(strArray() As String, intBottom As Integer, intTop As Integer)

  Dim strPivot As String, strTemp As String
  Dim intBottomTemp As Integer, intTopTemp As Integer

  intBottomTemp = intBottom
  intTopTemp = intTop

  strPivot = strArray((intBottom + intTop) \ 2)

  While (intBottomTemp <= intTopTemp)
   
    While (strArray(intBottomTemp) < strPivot And intBottomTemp < intTop)
      intBottomTemp = intBottomTemp + 1
    Wend
   
    While (strPivot < strArray(intTopTemp) And intTopTemp > intBottom)
      intTopTemp = intTopTemp - 1
    Wend
   
    If intBottomTemp < intTopTemp Then
      strTemp = strArray(intBottomTemp)
      strArray(intBottomTemp) = strArray(intTopTemp)
      strArray(intTopTemp) = strTemp
    End If
   
    If intBottomTemp <= intTopTemp Then
      intBottomTemp = intBottomTemp + 1
      intTopTemp = intTopTemp - 1
    End If
 
  Wend

  'the function calls itself until everything is in good order
  If (intBottom < intTopTemp) Then QuickSort strArray, intBottom, intTopTemp
  If (intBottomTemp < intTop) Then QuickSort strArray, intBottomTemp, intTop

End Sub

hendie

  • Guest
Re: How to Sort a ComboBox?
« Reply #12 on: September 10, 2007, 10:03:03 AM »
It appears you are reading the list into the combobox, then reading the combobox list into an array.
Why not make it quicker by not reading into the combobox to begin with.. just read the items directly into the array

Guest

  • Guest
Re: How to Sort a ComboBox?
« Reply #13 on: September 10, 2007, 10:04:13 AM »
Here's one that I've used quite a bit...


Code: [Select]
Sub BubbleSort(arr As Variant, Optional descending As Boolean, Optional numEls As Variant)
' Bubble Sort an array of any type
' Author: The VB2TheMax Team
' BubbleSort is especially convenient with small arrays (1,000
' items or fewer) or with arrays that are already almost sorted
'
' NUMELS is the index of the last item to be sorted, and is
' useful if the array is only partially filled.
'
' Works with any kind of array, except UDTs and fixed-length
' strings, and including objects if your are sorting on their
' default property. String are sorted in case-sensitive mode.
'
' You can write faster procedures if you modify the first two lines
' to account for a specific data type, eg.
' Sub BubbleSortS(arr() As Single, Optional descending As Boolean, Optional numEls As Variant)
'   Dim value As Single

    Dim Value As Variant
    Dim Index As Long
    Dim firstItem As Long
    Dim indexLimit As Long, lastSwap As Long

    ' account for optional arguments
    If IsMissing(numEls) Then numEls = UBound(arr)
    firstItem = LBound(arr)
    lastSwap = numEls

    Do
        indexLimit = lastSwap - 1
        lastSwap = 0
        For Index = firstItem To indexLimit
            Value = arr(Index)
            If (Value > arr(Index + 1)) Xor descending Then
                ' if the items are not in order, swap them
                arr(Index) = arr(Index + 1)
                arr(Index + 1) = Value
                lastSwap = Index
            End If
        Next
    Loop While lastSwap
End Sub