Macros Automatically Sort

[Fran]

In a spreadsheet of expiration dates and names, the sort function can be used
or a Macro can be set up to sort the dates in ascending order. Is there a
way that the dates will automatically sort without having to click sort or
run macro? For example, if the date is changed from 5/21/09 to 5/21/10, we
want excel to sort immediately when it is changed without having to do
anything else. This may not be possible but it would be extremely helpful to
our sales staff.

 

[Sheeloo]

You can do that if you add your sort code to the worksheet change event...
Right click on sheet tab, choose view code and paste the following after
adding your sort code
Private Sub Worksheet_Change(ByVal Target As Range)
'your sortcode here
'remember to first check whether the current cell is in sort range,
otherwise your code will run for all changes
'sample code to check for range
If Intersect(ActiveCell, Range("A1:A10")) Is Nothing Then
MsgBox "The active cell does NOT Intersect A1:A10"
Else
MsgBox "The active cell does Intersect A1:A10"
'you can put your sort code here
End If
End Sub

sample code to check for range
If Intersect(ActiveCell, Range("A1:A10")) Is Nothing Then
MsgBox "The active cell does NOT Intersect A1:A10"
Else
MsgBox "The active cell does Intersect A1:A10"
End If

 

[Gord Dibben]

I would caution about auto-sorting.

If a user makes a typo it could be very difficult to find the error after
the sort has been done.

Double-checking entries before sorting is much safer.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I agree with Gord. If I make a typing error and put in the wrong date, I may
not be able to find the entry (say if I wanted a 2009 date and typed in 2008).

And if I weren't done editing the data on that row, I would find it irritating
to go look for that row to finish my data entry.

If you're using xl2003 (or higher), you can apply data|filter|autofilter.
There's an option under each arrow that allows you to sort the filtered range by
that field.

If you're using xl2002 or below (or any version of excel, really), you can use a
technique at Debra Dalgleish's site:
http://contextures.com/xlSort02.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)