Alphabetizing ComboBoxes

[Big Lar]

This code populates my UserForm’s ComboBoxes.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The range is a list of names in random order.<o></o>
Is there a way to sort the ComboBoxes ranges ascending without sorting the source? <o></o>
<o></o>

[FONT=Verdana]Private Sub UserForm_Initialize()<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]Dim X&, rng As Range<o:p></o:p>[/FONT]
[FONT=Verdana]  <o:p></o:p>[/FONT]
[FONT=Verdana]  With Sheet1<o:p></o:p>[/FONT]
[FONT=Verdana]      <o:p></o:p>[/FONT]
[FONT=Verdana]      Set rng = .Range("D3", .Cells(Rows.Count, "D").End(xlUp))<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]      ComboBox1.RowSource = "'" & .Name & "'!" & rng.Address<o:p></o:p>[/FONT]
[FONT=Verdana]      ComboBox2.RowSource = "'" & .Name & "'!" & rng.Address<o:p></o:p>[/FONT]
[FONT=Verdana]      ComboBox3.RowSource = "'" & .Name & "'!" & rng.Address<o:p></o:p>[/FONT]
[FONT=Verdana]      ComboBox4.RowSource = "'" & .Name & "'!" & rng.Address<o:p></o:p>[/FONT]
[FONT=Verdana]          <o:p></o:p>[/FONT]
[FONT=Verdana]  End With<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]End Sub<o:p></o:p>[/FONT]

<o></o>

 

[MickG]

Try this:-
Make sure the "Rowsource" property is not set.

Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=navy]Dim[/COLOR] rng [COLOR=navy]As[/COLOR] Range, i [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] j [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] temp [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Ray
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Sheet1")
[COLOR=navy]Set[/COLOR] rng = .Range("D3", .Cells(rows.Count, "D").End(xlUp))
[COLOR=navy]End[/COLOR] With
    Ray = Application.Transpose(rng.value)
        [COLOR=navy]For[/COLOR] i = 1 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
[COLOR=navy]For[/COLOR] n = 1 To 4
    [COLOR=navy]With[/COLOR] Me.Controls("combobox" & n).Object
        .List = Ray
        .ListIndex = 0
     [COLOR=navy]End[/COLOR] With
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[mikerickson]

This would do one comboBox and its range.

Private Sub UserForm_Initialize()
    Dim oneCell As Range, itemToAdd As String
    Dim i As Long
    
    With ComboBox1
        For Each oneCell In Range("A1:A10")
            itemToAdd = CStr(oneCell.Value)
            
            If itemToAdd <> vbNullString Then
                For i = 0 To .ListCount - 1
                    If itemToAdd < .List(i) Then
                        .AddItem itemToAdd, i
                        GoTo NextOneCell
                    End If
                Next i
                .AddItem itemToAdd
            End If
            
NextOneCell:
        Next oneCell
    End With
End Sub

 

[Big Lar]

Mick.

Spectacular!

I just added you to my Hero list.


Mike,
I'm sure your code is just as good but Mick's works for my purpose...plus,
you're already on my Hero list.

Grande Lorenzo