Last Modify Date

[waterballoon]

I am trying to have a cell in each of my tabs show the last date that each respective tab was modified...is this possible?

Thanks in advance...

 

[Sektor]

[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_Change([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)
    Cells(1, 1) = DateValue(Now)
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[waterballoon]

So I open a new workbook
Go into VBA
Insert New Module
Paste your above code
Name it on the left side
Then...?

 

[Sektor]

There's better way. Place this module into ThisWorkbook module. Now there will day on all sheets in A1 cell:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    Sh.Cells(1, 1) = DateValue(Now)
    Application.EnableEvents = True
End Sub

 

[Hermanito]

Better add the lines as shown, to avoid problems.
Otherwise the code changes the same worksheet, which calls the code again, which changes the worksheet, which calls the code again, etcetera, you see where this is going...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    Sh.Cells(1, 1) = DateValue(Now)
    Application.EnableEvents = True
End Sub

 

[Jaafar Tribak]

The solutions provided will not detect modifications to the worksheets like formatting cells, embeeding objects etc...

Maybe a better approach is to check for the workbook Saved Property which changes whenever the workbook is modified including cell formatting etc..

In the workbook module :

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call UpdateCell(Sh)
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Call UpdateCell(Sh)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call UpdateCell(ActiveSheet)
    Me.Saved = False
End Sub

Private Sub UpdateCell(ByVal Sh As Worksheet)

    If Not Me.Saved Then
        Application.EnableEvents = False
        Sh.Cells(1, 1) = DateValue(Now)
        Me.Saved = True
        Application.EnableEvents = True
    End If

End Sub

 

[waterballoon]

Jaarfar-Where does it post the date in the worksheet?

 

[waterballoon]

For example, if I want it to show up in cell N1 is it (1, 14) or (14,1)

 

[Hermanito]

(1,14)
cell coordinates are given in row/column format

 

[Jaafar Tribak]

Don't forget to format the cell to display Dates.