Macro for putting last modified date in cell A1

[jamdur1]

I need a formula or macro to put in the last modified date in cell A1 for one sheet in my workbook. I've searched and found a few formulas but they don't seem to be working. Any ideas?

 

[pbornemeier]

Place this code in the workbook code page:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.Sheets(1).Range("A1") = ReturnWhenModifiedDate
End Sub

Place this code in a standard module:

Option Explicit

Function ReturnWhenModifiedDate() As Date
    'Called by Workbook Before_Save event
    
    Dim fso As Object, f As Object
    
    If thisworkbook.Path = vbNullString Then
        'File has never been saved
        ReturnWhenModifiedDate = Now() 'Return current date/time
    Else
        'File saved previously return that time
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set f = fso.GetFile(thisworkbook.Path & "\" & thisworkbook.Name)
        ReturnWhenModifiedDate = f.DateLastModified
        Set f = Nothing
        Set fso = Nothing
    End If

End Function

Note that a saved file is a modified file. In other words if you open a file and do nothing to it for as long as you want, then save it, the time you save it becomes the new modified time.

 

[jamdur1]

Thanks for the help. I hit Alt F11 and got in the code and then copied/pasted the code below in to the Sheet I wanted it placed. However, when I go to the sheet, nothing happens. I typed a bit and hit save but the modified date doesn't appear in A1. Is there anything I need to do in cell A1 or any place else for this to work? Thanks again for the help, appreciate it!

 

[pbornemeier]

In the first block of code:
Change the 1 in this line:
ThisWorkbook.Sheets(1).Range("A1") = ReturnWhenModifiedDate
to the name of the worksheet you want to update in quotes.
If your worksheet was named Second Trial then the first block of code would be:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.Sheets("Second Trial").Range("A1") = ReturnWhenModifiedDate
End Sub

All four lines of the edited first block of code goes in the "ThisWorkbook" code page.

Insert a new module, in the VBE menu select Insert | Module and place the second block of code in it.

Nothing needs to go in the code page of the worksheet that you want to have updated.

 

[jamdur1]

It worked!!! Thanks a ton for all of the help, really appreciate it!

 

[pbornemeier]

You're welcome. Have fun with Excel.

 

[arielreyes01]

In the first block of code:
Change the 1 in this line:
ThisWorkbook.Sheets(1).Range("A1") = ReturnWhenModifiedDate
to the name of the worksheet you want to update in quotes.
If your worksheet was named Second Trial then the first block of code would be:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.Sheets("Second Trial").Range("A1") = ReturnWhenModifiedDate
End Sub

All four lines of the edited first block of code goes in the "ThisWorkbook" code page.

Insert a new module, in the VBE menu select Insert | Module and place the second block of code in it.

Nothing needs to go in the code page of the worksheet that you want to have updated.

Hi!
This has worked great! Is there any way to get this to work on a per sheet basis? I's like to have a modified date entered on cell of each of my sheets if THAT specific sheet was changed. Instead of one date for the entire workbook...
Any thoughts?

 

[pbornemeier]

Add this code to the code page for each worksheet that you want to track:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'If any cell on a worksheet is changed manually or progrmatically, update date/time in cell A1 of tha worksheet
    Application.EnableEvents = False
    With Range("A1")
        .Value = Now()
        .NumberFormat = "mm/dd/yyyy hh:mm:ss"
    End With
    Application.EnableEvents = True
End Sub

This will trigger each time the sheet is changed, not only when the workbook is saved.

 

[kelle]

Add this code to the code page for each worksheet that you want to track:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'If any cell on a worksheet is changed manually or progrmatically, update date/time in cell A1 of tha worksheet
    Application.EnableEvents = False
    With Range("A1")
        .Value = Now()
        .NumberFormat = "mm/dd/yyyy hh:mm:ss"
    End With
    Application.EnableEvents = True
End Sub

This will trigger each time the sheet is changed, not only when the workbook is saved.

Any idea how to add the username of the person that last updated each sheet?

 

[pbornemeier]

Environ("Username") will return the name of the person logged in

    With Range("B1")
        .Value = Environ("Username")
    End With

Environ can return any of the variables you see when using SET from the command prompt