Stay on Active Sheet


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
 
Ad

Advertisements

Ad

Advertisements

C

Connie

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

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top