Non-holy pally healing gear
#12
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
Reply


Messages In This Thread
Non-holy pally healing gear - by swirly - 06-02-2008, 01:41 PM
Non-holy pally healing gear - by WimpySmurf - 06-02-2008, 02:38 PM
Non-holy pally healing gear - by swirly - 06-02-2008, 03:22 PM
Non-holy pally healing gear - by Concillian - 06-02-2008, 08:16 PM
Non-holy pally healing gear - by Kevin - 06-02-2008, 09:27 PM
Non-holy pally healing gear - by swirly - 06-03-2008, 05:58 AM
Non-holy pally healing gear - by Taelas - 06-03-2008, 11:35 AM
Non-holy pally healing gear - by Mordekhuul - 06-03-2008, 01:30 PM
Non-holy pally healing gear - by Taelas - 06-03-2008, 01:45 PM
Non-holy pally healing gear - by Pantalaimon - 06-03-2008, 02:06 PM
Non-holy pally healing gear - by swirly - 06-03-2008, 02:18 PM
Non-holy pally healing gear - by Pantalaimon - 06-03-2008, 03:24 PM
Non-holy pally healing gear - by Concillian - 06-03-2008, 04:25 PM
Non-holy pally healing gear - by swirly - 06-03-2008, 08:43 PM
Non-holy pally healing gear - by Kevin - 06-03-2008, 11:51 PM
Non-holy pally healing gear - by swirly - 06-04-2008, 10:06 AM
Non-holy pally healing gear - by swirly - 06-04-2008, 10:28 AM
Non-holy pally healing gear - by Concillian - 06-04-2008, 05:59 PM
Non-holy pally healing gear - by swirly - 06-04-2008, 06:50 PM
Non-holy pally healing gear - by Concillian - 06-04-2008, 07:50 PM
Non-holy pally healing gear - by swirly - 06-05-2008, 05:58 AM
Non-holy pally healing gear - by swirly - 06-05-2008, 07:26 AM
Non-holy pally healing gear - by WimpySmurf - 06-05-2008, 01:21 PM
Non-holy pally healing gear - by swirly - 06-06-2008, 12:30 AM
Non-holy pally healing gear - by swirly - 06-06-2008, 12:40 AM
Non-holy pally healing gear - by swirly - 06-07-2008, 01:54 PM

Forum Jump:


Users browsing this thread: 3 Guest(s)