problem with sort code for combo box with multiple columns

[Barq]

Hi,

I have a combo box on my spreadsheet that I use to return a value for a bunch of vlookups on the sheet. I was trying to sort the values in the combo box. I found some posts in this forum, and used code found in one of them. It worked great... except, I have 2 columns in my combo box and right now one of them is sorted by not the other, so the data displayed in the combo box is now not aligned properly.

Here's a copy of the modified code. Both of the columns to be displayed in the combo box are alphabetical. Please help!!! Thanks.


Private Sub ComboBox1_GotFocus()

ComboBox1.Clear
ComboBox1.List = Worksheets("Data1").Range("Company_Name").Value
ComboBox1.BoundColumn = 1
ComboBox1.ColumnCount = 2
Dim unsorted As Boolean, i As Integer, temp As Variant
unsorted = True
Do
unsorted = False
For i = 0 To UBound(ComboBox1.List) - 1
If ComboBox1.List(i) > ComboBox1.List(i + 1) Then
temp = ComboBox1.List(i)
ComboBox1.List(i) = ComboBox1.List(i + 1)
ComboBox1.List(i + 1) = temp
unsorted = True
Exit For
End If
Next i

Loop While unsorted = True

End Sub

 

[BrianB]

Why don't you do a simple sort of the list on the worksheet ?

 

[Fat Cat]

Your ComboBox is in effect an Array(i,2) of i rows and 2 columns

When you do the sorting, you don't indicate which column of data is to be sorted, so it defaults to the first column (0)

Just add in another sort working on the second column (1) and it should work fine

Private Sub ComboBox1_GotFocus()

ComboBox1.Clear
ComboBox1.List = Worksheets(1).Range("d5:e16").Value
ComboBox1.BoundColumn = 1
ComboBox1.ColumnCount = 2
Dim unsorted As Boolean, i As Integer, temp As Variant
unsorted = True
Do
unsorted = False
For i = 0 To UBound(ComboBox1.List) - 1
If ComboBox1.List(i,0) > ComboBox1.List(i + 1,0) Then    'the 0 means sort on the first column of the ListBox values

temp = ComboBox1.List(i, 0)       'sort the first column
ComboBox1.List(i, 0) = ComboBox1.List(i + 1, 0)
ComboBox1.List(i + 1, 0) = temp

temp = ComboBox1.List(i, 1)       'sort the second column to match the first
ComboBox1.List(i, 1) = ComboBox1.List(i + 1, 1)
ComboBox1.List(i + 1, 1) = temp

unsorted = True
Exit For
End If
Next i

Loop While unsorted = True

End Sub

 

[Barq]

Thanks!!!! Worked!!!! phew!