Excel: Automatic "Last Save Date and Time" Stamp

Show the last save date and time in a cell automatically with VB code
Català - Castellano - Deutsch
We have a standard excel file at work where we track the bidding process of each project with subs. It basically shows who was sent what and when, have we received anything back, etc. One of the points to fill is when was the document last updated to check how recent the file really is.

I wanted to get that "last modified" to be automatic. The idea was, that a cell would show the date of the last time someone saved the file. So I searched for how to do that, and I found this here. The code there described allows you to get a cell in excel that shows your last saved date (and time with a few tweeks)

The process to get that is as follows:

First of all we have to create a macro. Press Alt+F11 and you will get the Visual Basic Editor. When it opens double click on This Workbook and paste the code on the right. I have added a new line to the code to show also the time on a different cell. See the image, the code is after it.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("F4").Value = Date
Sheets("Sheet1").Range("G4").Value = Time
End Sub
Once you have entered the code, just close the VB Editor and save your file. The cells specified as Range in the sheet specified after Sheets will fill with the Save (F4 in my code) and TIme (g4 in my code). If the code doesn't work be sure the Sheet name is spelled exactly as the name of the sheet in your workbook.

You will probably get a message like this when you save. Just do as you think best.

The final result should be something like this.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...