06-03-2008, 03:24 PM
Quote:Haven't figured out how to make it resort on a value change, but not even sure that is possible. : )
Link is the same as before, but here it is again. The spreadsheet
This may be a little advanced for this application, but if you have time or are interested, there are hooks in Excel that allow you to perform macros on Events, such as worksheet changes. A quick google gave this, which is actually quite a decent explanation and better than I could manage off the cuff:
Code:
Event macros are triggered by an Event and are installed differently from regular macros. These Worksheet Event macros apply only to the sheet they are installed in.
The sort will be invoked upon sheet activation and on double click by use of Event macros.
As coded below the macro will sort the rows between the top row (header row), and the last row that has something in column A. The technique to lookup from the bottom is also used on one of my toolbar buttons
personal.xls!GotoBottomofCurrentColumn
(see my macros for my toolbar buttons)
To install these worksheet event macros. Right click on the sheetname, View code, insert the following:
A B C D E F
1 Who -B- -C- -D- Qty -F-
2 Guy 2 1 30 14 6 39
3 Guy 4 44 14 94 8 77
4 Guy 1 23 67 58 9 16
5 Guy 3 65 56 68 96 64
6 Totals 133 167 234 119 196
Private Sub Worksheet_Activate()
Dim LRow As Long '-- SORT on Col E then A
'Find row before last row in Column A with content
LRow = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Row
Rows("2:" & LRow).Sort Key1:=Range("E2"), _
Order1:=xlAscending, Key2:=Range("A2"), _
Order2:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True 'get out of edit mode from DoubleClick
Worksheet_Activate
End Sub
http://www.mvps.org/dmcritchie/excel/sorting.htm
http://www.mvps.org/dmcritchie/excel/event.htm
Basically I find it best to assume that Excel can do nearly anything until proven otherwise! :D