Load and Sort ComboBox with Dynamic Range From Sheet

[kevinleong]

Hey guys, I have a question about how to sort AND load a combobox with a dynamic list on a spreadsheet. Looking around on the internet, I've found separate solutions to each part of the puzzle, but when I tried integrating them together, I got a "Type mismatch" error.

From what I can tell, the code I'm using to sort the combobox first requires it to be loaded with the list. The code I'm using to load the list is:

Sub Userform_Initialize()

    'populate Organization combobox
    
    Dim organization As Range, z As ListBox
    Dim ws As Worksheet
    
    Set ws = Worksheets("Organizations")
    
    For Each organization In ws.Range("Orgs")
        If Not organization = "" Then Me.orgApplicantCombo.AddItem (organization)
    Next
    
    sortCombo

End Sub

"sortCombo" is the function I call to sort the list once it's been loaded. The code is as follows:

Private Sub sortCombo()

    Dim Ray, i As Integer, j As Integer, Temp As String
    With Me.orgApplicantCombo
    Ray = Application.Transpose(.List)
        For i = 1 To UBound(Ray) - 1
            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
    .List = Ray
    End With



End Sub

As I stepped through the code, a "Type mismatch" error gets thrown as soon as the program hits the line "Ray = Application.Transpose(.List)"

But I know that the code works when I load my combobox this way:

With Me.orgApplicantCombo        
          Me.orgApplicantCombo.List = Sheets("Organizations").Range("A2:A50").Value
    End With

Unfortunately loading it this way isn't dynamic, and it picks up all the empty cells which throws off the sort.

Any ideas on what causes the "mismatch" in way I set up the combobox dynamically? Honestly, I'm a huge VBA noob and I could be doing things completely wrong.

Any help would be much appreciated!

[kevinleong]

Nevermind, I came up with a better solution to dynamically load my combobox using the following code:

With Me.orgApplicantCombo        
            With Sheets("Organizations")
            Me.orgApplicantCombo.List = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Value
        End With
    End With

which works without error with my Sort code.