problem with sort code for combo box with multiple columns

Barq

New Member
Joined
Nov 16, 2005
Messages
18
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,491
Messages
6,172,589
Members
452,468
Latest member
godlennutrition

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top