Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dump ListBox Contents to Sheet & Stay on Active Sheet | Excel Programming | |||
active sheet is 'slow' to be active | Excel Programming | |||
I need to sort an active sheet using the col of the active cell | Excel Programming | |||
Excel sheet won't stay on my screen | Excel Discussion (Misc queries) | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming |