Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dump ListBox Contents to Sheet & Stay on Active Sheet
I am sorting a multi-column listbox using pretty much a manual method, but I finally got it to work, so I'm sticking with it. The listbox was not created from a row source, so to sort it, I basically create a temporary sheet, dump the list box contents, sort the data, copy it back to the listbox and then delete the temporary sheet. My only problem (besides speed) is that when I add the sheet and dump the contents of the listbox, I see the sheet updating as the contents are dumped. I have screenupdating off, but I still see the sheet. How can I stay on the active sheet and not watch the sheet being updated? Here's the code. Any help would be greatly appreciated!
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)) Next j Next i Application.DisplayAlerts = False Sheets("TempSheet").Delete Application.DisplayAlerts = True ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompt for file name and paste contents of active sheet | Excel Programming | |||
ListBox RowSource set to range of in-active sheet - possible? | Excel Programming | |||
Userform Listbox using an active sheet | Excel Discussion (Misc queries) | |||
Rename active sheet to contents of specific cell | Excel Programming | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming |