Login

Pantalaimon · 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.&nbsp;&nbsp;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.&nbsp;&nbsp;The technique to lookup from the bottom is also used on one of my toolbar buttons

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;personal.xls!GotoBottomofCurrentColumn

&nbsp;&nbsp;&nbsp;&nbsp;(see my macros for my toolbar buttons)

To install these worksheet event macros. Right click on the sheetname, View code, insert the following:

&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;

 &nbsp;&nbsp;&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;C&nbsp;&nbsp;&nbsp;&nbsp;D&nbsp;&nbsp;&nbsp;&nbsp;E&nbsp;&nbsp;&nbsp;&nbsp;F

 1&nbsp;&nbsp;&nbsp;&nbsp; Who &nbsp;&nbsp;&nbsp;&nbsp; -B-&nbsp;&nbsp;&nbsp;&nbsp; -C-&nbsp;&nbsp;&nbsp;&nbsp; -D- &nbsp;&nbsp;&nbsp;&nbsp; Qty&nbsp;&nbsp;&nbsp;&nbsp; -F-

 2 &nbsp;&nbsp;&nbsp;&nbsp; Guy 2&nbsp;&nbsp;&nbsp;&nbsp; 1 &nbsp;&nbsp;&nbsp;&nbsp; 30 &nbsp;&nbsp;&nbsp;&nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6 &nbsp;&nbsp;&nbsp;&nbsp; 39 

 3 &nbsp;&nbsp;&nbsp;&nbsp; Guy 4&nbsp;&nbsp;&nbsp;&nbsp; 44 &nbsp;&nbsp;&nbsp;&nbsp; 14 &nbsp;&nbsp;&nbsp;&nbsp; 94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8 &nbsp;&nbsp;&nbsp;&nbsp; 77 

 4 &nbsp;&nbsp;&nbsp;&nbsp; Guy 1&nbsp;&nbsp;&nbsp;&nbsp; 23 &nbsp;&nbsp;&nbsp;&nbsp; 67 &nbsp;&nbsp;&nbsp;&nbsp; 58&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9 &nbsp;&nbsp;&nbsp;&nbsp; 16 

 5 &nbsp;&nbsp;&nbsp;&nbsp; Guy 3&nbsp;&nbsp;&nbsp;&nbsp; 65 &nbsp;&nbsp;&nbsp;&nbsp; 56 &nbsp;&nbsp;&nbsp;&nbsp; 68&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 96 &nbsp;&nbsp;&nbsp;&nbsp; 64 

 6 &nbsp;&nbsp;&nbsp;&nbsp; Totals &nbsp;&nbsp;&nbsp;&nbsp; 133 &nbsp;&nbsp;&nbsp;&nbsp; 167&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 234 &nbsp;&nbsp;&nbsp;&nbsp; 119 &nbsp;&nbsp;&nbsp;&nbsp; 196 

Private Sub Worksheet_Activate()

&nbsp;&nbsp;Dim LRow As Long&nbsp;&nbsp;&nbsp;&nbsp;'-- SORT on Col E then A

&nbsp;&nbsp;'Find row before last row in Column A with content

&nbsp;&nbsp; LRow = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Row

&nbsp;&nbsp; Rows("2:" & LRow).Sort Key1:=Range("E2"), _

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Order1:=xlAscending, Key2:=Range("A2"), _

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Order2:=xlAscending, Header:=xlNo, _

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OrderCustom:=1, MatchCase:=False, _

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Orientation:=xlTopToBottom

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cancel As Boolean)

&nbsp;&nbsp;Cancel = True&nbsp;&nbsp; 'get out of edit mode from DoubleClick

&nbsp;&nbsp;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

Login
Username:
Password:	Lost Password?
	Remember me