PDA

View Full Version : Load and Sort ComboBox with Dynamic Range From Sheet



kevinleong
04-11-2017, 01:55 PM
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
04-11-2017, 04:49 PM
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.