Show the last save date and time in a cell automatically with VB codeCatalà - 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)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.
Sheets("Sheet1").Range("F4").Value = Date
Sheets("Sheet1").Range("G4").Value = Time
End Sub
You will probably get a message like this when you save. Just do as you think best.
Tweet
You should save the file as macro-enabled
ReplyDeleteAnd you can change the code to simply show as follows,
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("F4").Value = Date & " " & Time
End Sub