Macros Automatically Sort
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. |
Macros Automatically Sort
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 "Fran" wrote: 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. |
Macros Automatically Sort
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 On Thu, 21 May 2009 08:43:25 -0700, Fran wrote: 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. |
Macros Automatically Sort
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 he 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.) Fran wrote: 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. -- Dave Peterson |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com