Insert the date an Excel Workbook was last modified.

Anonymous
2010-11-17T00:13:29+00:00

Is there a way to insert, into a cell, the date that a workbook was last modified?

Perhaps by getting the modified date from the file's properties into a cell.

Thanks in advance.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2010-11-17T00:29:39+00:00

Hi,

To modify a workbook you have to save it so we can use the before_save event

ALY+F11 to open vb editor. Double click 'ThisWorkbook' and paste this code in on the right. Change the sheet and range to suit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheets("Sheet1").Range("A1").Value = Now

End Sub

If this post answers your question, please mark it as the Answer.

Mike H

Was this answer helpful?

100+ people found this answer helpful.
0 comments

86 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2016-09-07T15:16:03+00:00

    None of the solutions throughout this whole thread of information did what I wanted. Most of the time I just got errors. I came up with my own solution that does exactly what I needed.

    My code:

    Private Sub Worksheet_Change(ByVal Target As Range) <br><br>    Range("A1").Value = Now <br><br>End Sub

    Simply press ALT+F11

    Double click Sheet1(Sheet1) on the left side.

    Copy the code into the right side.

    Go back to your worksheet and make sure you are NOT in Design Mode under the Developer Ribbon.

    Now every time the sheet is updated the date will be updated in cell A1. You can change the format of the date by right clicking the cell and formatting it to show in the desired format. I preferred to use a custom format that shows both the date and the time and looks like this...

    dddd, mmmm dd, yyyy \at h:mm AM/PM

    which shows up like this...

    Wednesday, September 07, 2016 at 11:09 AM

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-09-07T22:46:08+00:00

    AceWingMan,

    You are "Da BOMB" As you said, none of the other solutions helped me with my problem. I have multiple accounts in a workbook, each having it's own worksheet. When I enter the status of the account I wanted the date to show the date of the change. Nothing was working until now. Thank you. This worked like a charm. I changed the cell to B1 vice A1, other than that it worked exactly like I envisioned it.

    Wait, it worked on when I entered it in. Then I saved the workbook and now it's not working. ??? That is not how I envisioned it. Any ideas?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-09-08T02:15:49+00:00

    Works as expected after save/reload for me. Make sure you have Design Mode turned off or it won't work.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-09-09T16:06:15+00:00

    Ace Wingman,

    I think I found my problem. My Excel was set up as a macro-free workbook and VB project could not be saved. I saved it again as a .xlsm file instead of a .xlsx file and it worked. I don't have my thumb drive with me today to try it on my workbook I actually want to use it on, but I tried this on another workbook and it worked after changing file types. Thanks again for sharing your wisdom and experience.

    James

    Was this answer helpful?

    0 comments