Stay on Active Sheet

  • Thread starter Thread starter Connie
  • Start date Start date
C

Connie

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 thelistbox, I am creating a temporary sheet, dumping the listbox contents, sorting the data, transferring the data back to the listbox and then deletingthe 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 stayon 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.SortFields.Clear
ActiveWorkbook.Worksheets("TempSheet").Sort.SortFields.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
 
Application.ScreenUpdating



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion







---

This email is free from viruses and malware because avast! Antivirus protection is active.

http://www.avast.com
Click to expand...

OMG! Thank you very much. I've been looking at it for the last 6 hours, and for the life of me, I didn't catch it. Thanks again!
 
Back
Top