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?

 

[kelle]

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

I believe that gets me the last person who saved the file, rather than the last person to edit a specific sheet; is there any way to do it by sheet rather than by the whole file?

 

[pbornemeier]

Environ("Username") will return the name of the person logged in when that function is executed. If it is during the workbook save event, then it will record who was logged in when the workbook was saved. If it is executed during a Worksheet_Change event then it will record the user at the time a change was made.

It sounds as if you want to keep close track of all changes to this worksheet. Code can be written to track what was changed by who and when for each cell on the worksheet by writing to another worksheet or workbook. This will cause extra load on the system.

This post shows an example to track all changes of a single cell on any worksheet: http://www.mrexcel.com/forum/excel-questions/274711-find-out-who-last-modified-excel-file.html It could be modified to track any changes on a specific worksheet as well.

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
    ' and the name of the person logged in when that change was made
    Application.EnableEvents = False
    With Range("A1")
        .Value = Now()
        .NumberFormat = "mm/dd/yyyy hh:mm:ss"
    End With
    With Range("B1")
        .Value = Environ("Username")
    End With
    Application.EnableEvents = True
End Sub

 

[kelle]

Environ("Username") will return the name of the person logged in when that function is executed. If it is during the workbook save event, then it will record who was logged in when the workbook was saved. If it is executed during a Worksheet_Change event then it will record the user at the time a change was made.

It sounds as if you want to keep close track of all changes to this worksheet. Code can be written to track what was changed by who and when for each cell on the worksheet by writing to another worksheet or workbook. This will cause extra load on the system.

This post shows an example to track all changes of a single cell on any worksheet: http://www.mrexcel.com/forum/excel-questions/274711-find-out-who-last-modified-excel-file.html It could be modified to track any changes on a specific worksheet as well.

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
    ' and the name of the person logged in when that change was made
    Application.EnableEvents = False
    With Range("A1")
        .Value = Now()
        .NumberFormat = "mm/dd/yyyy hh:mm:ss"
    End With
    With Range("B1")
        .Value = Environ("Username")
    End With
    Application.EnableEvents = True
End Sub

This works great! Thank you.