Inside ListBox Sorting Problem..

[ms06f]

When I click (Load VBA Data) button then click Sort Column 1,2 & 3 button,
Listbox1 can update.

But click (Load Sheet1 Data) button then click Sort Column 1,2 & 3 again,
Listbox1 data cannot update.

What problem ?

Please HELP me again....
Thankyou.

[marreco]

Hi

Can adapt this example?

look
Font:http://www.ozgrid.com/forum/showthread.php?t=71509&highlight=ListBox+Sorting

Sub SortListBox(oLb As MSForms.ListBox, sCol As Integer, sType As Integer, sDir As Integer)
Dim vaItems As Variant
Dim i As Long, j As Long
Dim c As Integer
Dim vTemp As Variant

'Put the items in a variant array
vaItems = oLb.List

'Sort the Array Alphabetically(1)
If sType = 1 Then
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
'Sort Ascending (1)
If sDir = 1 Then
If vaItems(i, sCol) > vaItems(j, sCol) Then
For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If

'Sort Descending (2)
ElseIf sDir = 2 Then
If vaItems(i, sCol) < vaItems(j, sCol) Then
For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If
End If

Next j
Next i
'Sort the Array Numerically(2)
'(Substitute CInt with another conversion type (CLng, CDec, etc.) depending on type of numbers in the column)
ElseIf sType = 2 Then
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
'Sort Ascending (1)
If sDir = 1 Then
If CInt(vaItems(i, sCol)) > CInt(vaItems(j, sCol)) Then
For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If

'Sort Descending (2)
ElseIf sDir = 2 Then
If CInt(vaItems(i, sCol)) < CInt(vaItems(j, sCol)) Then
For c = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, c)
vaItems(i, c) = vaItems(j, c)
vaItems(j, c) = vTemp
Next c
End If
End If

Next j
Next i
End If

'Set the list to the array
oLb.List = vaItems
End Sub

[Norie]

It could be because you've used RowSource to populate the ListBox from the worksheet.

If you change the code that loads data from the worksheet to something like this then the sort code you have works.

Unfortunately you can't have headers using this method to populate a listbox.
Sub LoadSampleData1()

ListBox1.List = Worksheets("Blad1").Range("A2", Worksheets("Blad1").Range("E" & Rows.Count).End(xlUp)).Value
ListBox1.ColumnCount = 5
ListBox1.ColumnWidths = "40 pt;50 pt;50 pt;80 pt;30 pt"
End Sub