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.

 

[GlennUK]

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.

Well, now you have deleted the code, of course you are able to enter data. Now, if you want to get this working, create a defined name of LastChange, with a RefersTo of say "Dummy". Add your code back in, try again.

 

[Aimee S.]

Same run-time error 1004. Selected entire workbook range and named it "NKVDNMMAD" (stands for notknowingvbadoesnotmakemeadummy). Then I pasted the formula back so that it shows:

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

I give up.

 

[wigi]

I give up.

That's for your own account.

You do not have to select cells to make the named range.

Hit Ctrl-F3 and make a new name. Give it the name LastChange and set it equal to:

=0

for instance.

I expressly ask you to use LastChange for the name, because I doubt whether you understand that the name I use in the code, should be the same as the name you pick. In the end, you used the name "NKVDNMMAD", without changing it in the code. That is, you changed the code but in a wrong way.

Then, paste my code in the correct area and it will work.

 

[Aimee S.]

Making a range called "LastChange" by selecting all cells in that worksheet worked. I had no idea the LastChange part was the range name, just thought it was the name we were giving to the formula created.

Thanks for solving this. I verified and the last change shows 8/1/11 11:22am after I deleted something from a cell. Upon saving again a few minutes later, the last modified date remains as it should and does not change whenever save occurs (unless mods are made).

 

[wigi]

Glad it's solved now.

 

[fishwest]

Glad it's solved now.

I know that this thread is a little aged, but I was hoping to get some help modifying the final code (which I got working just fine). What I would like is the ability to define the same function, but have it be worksheet specific. If I use the existing code, but change the "Name" to reference a specific worksheet and area, it stops working. I am guessing that there is a different variable for the current active sheet.

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

Thank you!