Date Last Modified formula?

[Aimee S.]

Is there such a thing?

If this involves VBA, pretend I am an idiot and go step by step. I know ALT+F11 gets me to that screen but not much more.

I want cell B2 of my main worksheet to show in mm/dd/yy format the last date when any values throughout the entire workbook were changed.

 

[storm8]

Hello,
Im assuming you want to track when a cell value (or formula) was edited.

try this:

open the vba editor (alt+F11), in the project explorer (the left part of window) select worksheet for on which you want to track change and place following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A2").Value = Now()
End Sub

then for one sheet you can use =max() with all the ranges

 

[Aimee S.]

Hi! Thank, but thatt only returns the highest maximum value on the page of data.

I am looking for a code/fomula that gives me the last date *anything* whatsoever was changed throughout the workbook.

 

[wigi]

For my part, I think FileDateTime is what you want.

http://www.mrexcel.com/forum/showthread.php?t=29767

 

[Aimee S.]

That is cool to know. Unfortunately, as we all know, people can save even when there are no changes made (habit) so this could be very misleading if it were to take the place of when actual changes were last made.

 

[wigi]

Then you need the kind of events that storm8 wrote about.

Probably I would go for a Workbook_SheetSelectionChange event.

 

[Aimee S.]

Understood, but I don't know enough about those to tinker, and the above solution only gave me the greatest of all data values on the sheet, so I am confused as to why the MAX aspect is in there or why it won't work as I want it to.

 

[GlennUK]

Hi! Thank, but thatt only returns the highest maximum value on the page of data.

I am looking for a code/fomula that gives me the last date *anything* whatsoever was changed throughout the workbook.

How about using the Workbook_SheetChange event of the ThisWorkBook object, and storing the date and time of any change in a defined name?

 

[Aimee S.]

Pick a defined name, any defined name! Can you show me with what it would look like? The full VBA as well as the formula I would type in the worksheet cell?

 

[wigi]

I did mean the Workbook_SheetChange event in my post above, not SelectionChange.

Consider:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ThisWorkbook.Names("LastChange").RefersTo = Now
End Sub

First create a named range called LastChange. Then paste this code in "ThisWorkbook" in VBA. Then you're ready to use the value of that named range in a cell in Excel. For instance:

=LastChange

Wigi