Last modified date and user

[jessiegeorgia]

Hi All,

Is there away to auto fill a last modified date and user on a sheet/workbook, I was hoping there would be something equivalent to Word's Quick Parts? Relying on users to enter the date when they make changes isn't proving very reliable nor can I rely on users to check the file/info to make sure they are quoting the correct date when the data is used or shared wider.

Many thanks in advance,
Jess

 

[nemmi69]

See if this helps.

In the main module

Public WbookChanged As Boolean

In each sheet in the workbook

Private Sub Worksheet_Change(ByVal Target As Range)
    WbookChanged = True
End Sub

In Thisworkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If WbookChanged = True Then
        'Where A1 is the user name, A2 = date time
        Range("A1").Value = Application.UserName
        Range("A2").Value = Now()
        'If there is a password it will require more below
        Workbook.Save
    End If
End Sub

 

[nemmi69]

Opps

Workbook.Save should be This Workbook.save

 

[jessiegeorgia]

Thanks nemmi69, nothing appears after closing, where will it populate?

 

[nemmi69]

It should appear in "A1" and "A2" when you re-open the workbook. Just a note, the workbook should have already been saved as an "xlsm" to preserve the macros.

I did a step through to check it was working, starting with the sheetchange routine. When I confirmed that was OK I did the same with the beforeclose.