ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Stay on Active Sheet (https://www.excelbanter.com/excel-programming/449550-stay-active-sheet.html)

Connie

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

GS[_2_]

Stay on Active Sheet
 
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


Connie

Stay on Active Sheet
 
On Tuesday, November 26, 2013 11:07:38 PM UTC-5, GS wrote:
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!


All times are GMT +1. The time now is 02:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com