Is there a way of showing the date a cell was last changed?
Posted by Ian on February 07, 2002 2:35 AM
Hi.
I'm trying to find a way of adding a cell at the end of a row of data fields, so that when any of the data fields are changed the last cell shows the current date.
I thought of trying to run a macro when the cells are changed, but can't find a way of doing this automatically. Anyone know if this is possible, or if there is another way?
TIA
Ian
Posted by Mudface on February 07, 2002 3:00 AM
The following is a fairly simple way of doing it and should work OK. Enter the following code into the Worksheet_Change sub: -
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Column
Case 1 To 10
Cells(Target.Row, 11) = Time & " " & Date
End Select
End Sub
This will enter a date and time into the eleventh column on your sheet, if any cell of the first ten columns are changed. Format the column you want the time and date to appear in as mm/dd/yy hh:mm (or whatever you require) before you start changing cells.
Posted by DALE MINKEL on February 07, 2002 3:07 AM
Posted by DALE MINKEL on February 07, 2002 3:18 AM
YOU CAN ALSO "SHARE WORKBOOK". GO TO TOOLS, CLICK SHARE WORKBOOK.THEN UNDER EDITING TAB, CHECK "ALLOW CHANGES BY MORE THAN ONE.....",CLICK OKAY AND IT WILL PROMPT YOU TO "SAVE AS", AND "WHERE". EACH TIME WORKBOOK HAS BEEN CHANGED YOU CAN NOW SEE IT ON TOOLS MENU UNDER "HIGHLIGHT CHANGES" OR "TRACK CHANGES". IT WORKS BEST IF YOU UNCHECK THE "WHEN" CHECKBOX .LEAVE THE "TRACK CHANGES WHILE EDITING AND HIGHLIGHT CHANGES ON SCREEN" CHECKED
Posted by John H on February 07, 2002 3:45 AM
Be aware though, tht if you later 'unshare' the workbook, all this info will be lost. You can also not run pivot tables from a shared workbbok.
Posted by Ian on February 07, 2002 5:01 AM
That's great - problem solved.
Many thanks
