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.

 

[wigi]

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?

That's what I did. Please also spend time in investigating the topic, by browsing the internet for instance or reading the help files.

 

[Aimee S.]

With all due respect I did, and this was my last resort. Perhaps I did not know what to google for specifically, or worded it incorrectly. Thank you for your time.

 

[wigi]

With all due respect I did, and this was my last resort. Perhaps I did not know what to google for specifically, or worded it incorrectly. Thank you for your time.

OK. Does it work now?

 

[Aimee S.]

No sadly. Just get the error message box:

Run-time error '1004':

Application-defined or object-defined error

 

[wigi]

Did you create the named range?
What if you use the formula I gave you, but without the VBA code?

 

[Aimee S.]

I tried deleting the code from VBA and using =LastChange and get #NAME?

Do I have to define the range for every worksheet for a lastchange formula that checks the whole workbook for last modified date?

 

[wigi]

No, you create the named range once. In a cell and sheet of your liking.

After that, you paste the code I delivered earlier.

 

[Aimee S.]

OK so I selected all cells for a worksheet and named it "ALL"

Then I pasted this into the "ThisWorkbook" VBA window and saved.

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

Then I entered =LastChange in the cell I want the last changed date to appear in that same worksheet and get the same runtime error.

 

[GlennUK]

...

 

[Aimee S.]

Yes. Also, I chose not to debug at that prompt and went about doing other work in the sheet. as soon as I entered a value in another cell and hit enter the same runtime error came up. So I deleted the code, saved, and now am able to enter data without that coming up.