Stay on Active Sheet
I am sorting a multi column listbox using a fairly manual method, but am able to run it. The listbox is not created from a row source, so to sort the listbox, I am creating a temporary sheet, dumping the listbox contents, sorting the data, transferring the data back to the listbox and then deleting the temporary sheet. Everything works fine, but when I create the temporary sheet and dump the contents, I see the contents being dumped on the screen. I have screenupdating off, but it still happens. I would like to stay on the active sheet. Here's the code. Any help would be appreciated. Thanks.
Private Sub SortListBox(SortColumn As String)
Dim i, j, TempRow, TempCol As Integer
Dim SortField As String
ScreenUpdating = False
Sheets.Add.Name = "TempSheet"
For i = 1 To frmFindResource.ListBox1.ListCount
For j = 1 To 6
ActiveWorkbook.Sheets("TempSheet").Cells(i, j) = frmFindResource.ListBox1.List(i - 1, j - 1)
End If
Next j
Next i
TempRow = Sheets("TempSheet").UsedRange.Rows.Count
TempCol = Sheets("TempSheet").UsedRange.Columns.Count
SortField = SortColumn + CStr(TempRow)
ActiveWorkbook.Worksheets("TempSheet").Sort.SortFi elds.Clear
ActiveWorkbook.Worksheets("TempSheet").Sort.SortFi elds.Add Key:=Range( _
SortField), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("TempSheet").Sort
.SetRange Range(Cells(1, 1), Cells(TempRow, TempCol))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For i = 1 To TempRow
For j = 1 To TempCol
frmFindResource.ListBox1.List(i - 1, j - 1) = CStr(Sheets("TempSheet").Cells(i, j))
End If
Next j
Next i
Application.DisplayAlerts = False
Sheets("TempSheet").Delete
Application.DisplayAlerts = True
ScreenUpdating = True
End Sub
|