Alphabetize combobox list

[macangc]

Hi All

I have the following code which looks at a list and shows the unique values from another sheet within the combobox. Is there a way i can get the combobox to show these values in alphabetical order?

many thanks

Private Sub UserForm_Initialize()

Dim v, e
With Sheets("Service Ticket Detail").Range("J2:J5000")
v = .Value
End With
With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In v
If Not .exists(e) Then .Add e, Nothing
Next
If .Count Then Me.FaultCat.List = Application.Transpose(.keys)
End With


End Sub

 

[Noz2k]

There's probably a much more clever way of doing it, but could you not post the unique values to a hidden sheet, sort that data and then populate the combobox with the already sorted data?

 

[MickG]

Try something like this:-

[COLOR=navy]Sub[/COLOR] MG13Jul43
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] i [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] j [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Temp [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Ray
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=navy]Then[/COLOR]
        .Add Dn.Value, ""
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
Ray = .keys
[COLOR=navy]For[/COLOR] i = 0 To UBound(Ray)
    [COLOR=navy]For[/COLOR] j = i To UBound(Ray)
        [COLOR=navy]If[/COLOR] Ray(j) < Ray(i) [COLOR=navy]Then[/COLOR]
            Temp = Ray(i)
            Ray(i) = Ray(j)
            Ray(j) = Temp
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] j
[COLOR=navy]Next[/COLOR] i
ComboBox1.List = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[macangc]

Thanks for replying, much appreciated

Added your code but its not returning any data. I have amended it to reflect my s/s but im not sure why it isnt working

Private Sub MG13Jul43()
Dim Rng As Range, Dn As Range
Dim i As Long
Dim j As Long
Dim Temp As String
Dim Ray
Set Rng = Range(Range("J1"), Range("J" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, ""
End If
Next
Ray = .keys
For i = 0 To UBound(Ray)
For j = i To UBound(Ray)
If Ray(j) < Ray(i) Then
Temp = Ray(i)
Ray(i) = Ray(j)
Ray(j) = Temp
End If
Next j
Next i
FaultCat.List = Ray
End With
End Sub

 

[macangc]

Ok i have managed to do it with the following code

thanks for both your help

Private Sub UserForm_Initialize()


Dim v, e
With Sheets("Service Ticket Detail").Range("J2:J5000")
v = .Value
End With
With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In v
If Not .exists(e) Then .Add e, Nothing
Next
If .Count Then Me.FaultCat.List = Application.Transpose(.keys)
End With

Dim i As Long
Dim j As Long
Dim Temp As Variant
With FaultCat
For i = 0 To .ListCount - 2
For j = i + 1 To .ListCount - 1
If .List(i) > .List(j) Then
Temp = .List(j)
.List(j) = .List(i)
.List(i) = Temp
End If
Next j
Next i
End With

End Sub