Could somebody please help with the following.

I wish to format an expenses form so that as entries are made they are
automatically arranged by date with the oldest date at the top of the page
and most recent at the bottom (or more precisely, arranged at the lowest
entry line).

I know that one can do this quite easily after entering all the date.
However I should like to pre format the form for automatic line arrangement.

Thanks

On 23 Jul, 19:54, Duncan wrote:
Could somebody please help with the following.

I wish to format an expenses form so that as entries are made they are
automatically arranged by date with the oldest date at the top of the page
and most recent at the bottom (or more precisely, arranged at the lowest
entry line).

I know that one can do this quite easily after entering all the date.
However I should like to pre format the form for automatic line arrangement.

Thanks

try data.autofilter.sort descending.....
that should do it

Duncan

You would need event code in the worksheet module to achieve this sorting as you
enter.

Are you sure you want that?

What happens if an error is made when entering a date?

The dates sort, so how do you find the cell with the incorrect date?

Here is event code to sort column B as you enter a date.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long
If Target.Column < 2 Then Exit Sub
Application.ScreenUpdating = False
EndData = Cells(Rows.Count, 2).End(xlUp).Row
With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
Application.ScreenUpdating = False
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP


On Mon, 23 Jul 2007 13:26:55 -0700, Matthew wrote:

On 23 Jul, 19:54, Duncan wrote:
Could somebody please help with the following.

I wish to format an expenses form so that as entries are made they are
automatically arranged by date with the oldest date at the top of the page
and most recent at the bottom (or more precisely, arranged at the lowest
entry line).

I know that one can do this quite easily after entering all the date.
However I should like to pre format the form for automatic line arrangement.

Thanks

try data.autofilter.sort descending.....
that should do it