Microsoft Excel - Row Modification Date - Asked By Josh Sweers on 23-Jan-09 02:37 PM

Is there a way to make the last cell in a row display the last time anything was modified in that row?

I found this thread that almost does what I want.

http://www.eggheadcafe.com/software/aspnet/32518470/update-date-in-one-cell-w.aspx

But I can only change the range and set it up for one row. I want a different date for every row and I don't want to copy the code a million times.

TRY THIS - C_A P replied to Josh Sweers on 24-Jan-09 04:10 AM

In the cell I have

=DOCPROPS("last save time")

and I did a module \

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".

 Function DocProps(prop As String) 
    Application.Volatile 
    On  Error Goto err_value 
    DocProps =  ActiveWorkbook.BuiltinDocumentProperties _ 
    (prop) 
    Exit Function 
err_value: 
    DocProps = CVErr(xlErrValue) 
End Function

TRY THIS - C_A P replied to Josh Sweers on 24-Jan-09 04:12 AM

You can add this as a User Defined Function:

Public Function ModDate()
ModDate = Format(FileDateTime(ThisWorkbook.FullName), "m/d/yy h:n ampm")
End Function

TRY THIS - C_A P replied to Josh Sweers on 24-Jan-09 04:13 AM

If the window is maximised, you should see an Excel icon to the left of "File" in the menu bar. Rightclick it & choose "View Code".

Paste the following into the window that appears (the workbook module):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time
End Sub

Press ALT+Q to quit the Visual Basic Editor. Then save the file & check the footer in Print Preview.

Code Already Used - Josh Sweers replied to C_A P on 26-Jan-09 09:39 AM

I might not have followed what you were saying, but none of the previous suggestions seemed to work for me.

Right now I am using this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToInspect As Range

Set myRngToInspect = Me.Range("a3:t3")

If Intersect(Target, myRngToInspect) Is Nothing Then
GoTo Line4
End If

Application.EnableEvents = False
Me.Range("u3").Value = Now
Application.EnableEvents = True

Then I have the same code for Line 4, Line5 etc. With this, as soon as anyone modifies a cell in say row 5 (between columns A and T) The time and day that they modified it appears in column U (row 5). The same happens for every row. This is exactly wht I want, but I have 100 rows and the total number might change even...so I was wondering how to apply this code to every row with data in it.

Complete Code - Josh Sweers replied to Josh Sweers on 26-Jan-09 11:06 AM

Actaully....here...this is more complete. This would do what I want for rows 3, 4, and 5.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToInspect As Range

Set myRngToInspect = Me.Range("a3:t3")

If Intersect(Target, myRngToInspect) Is Nothing Then
GoTo Line4
End If

Application.EnableEvents = False
Me.Range("u3").Value = Now
Application.EnableEvents = True

Line4:
Set myRngToInspect = Me.Range("a4:t4")

If Intersect(Target, myRngToInspect) Is Nothing Then
GoTo Line5
End If

Application.EnableEvents = False
Me.Range("u4").Value = Now
Application.EnableEvents = True

Line5:
Set myRngToInspect = Me.Range("a5:t5")

If Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
Me.Range("u5").Value = Now
Application.EnableEvents = True

End Sub

Joseph replied to Josh Sweers on 17-May-11 05:31 PM

Right click on the sheet you want to use this feature in and click view code. Pase the code below into visual basic and modify accordingly.
Modifications:
(Target,Range("this is the range of columns you want to capture changes in")
Where I have "26" replace that number with the number of the column you want to place the date (i.e. If I wanted to place the date in every row I modify in column D I would put "4". )

After pasting, hit ctrl s (or save) and you are done. Easy as that! Enjoy!
(You could also add the time into your date stamp by modifying the code slightly but I'm sure you can figure that out.)

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = 0
    If Not Intersect(Target, Range("A:BI")) Is Nothing Then
      If Target.Rows.Count > 26 Then
        If Target.Column = 26 Then Cells(Target.Row, 26).Resize(UBound(Target.Value)).Value = Date
        Cells(Target.Row, 26).Resize(UBound(Target.Value)).Value = Date
      Else
     If Target.Column = 26 Then Cells(Target.Row, 26).Value = Date
      Cells(Target.Row, 26).Value = Date
      End If
    End If
    Application.EnableEvents = 1
End Sub