Forum Discussion
Date of Last Modified for a Cell
HansVogelaar Thank you very much for the time you took to reply to this thread in such detail. After reading the entire thread, I was able to create the VBA I needed to make my situation work. I need to figure out how to have this same function for two tables on the same worksheet. You have contributed so much, so I'm not asking you to give me the answer but if it's no trouble, would you mind letting me know how to apply this to a second table in the same worksheet? Here is what I used:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject
Dim rng As Range
Set tbl = Me.ListObjects("Table1")
Set rng = Intersect(tbl.ListColumns("Balance").DataBodyRange, Target)
If Not rng Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
rng.Offset(0, 3).Value = Now
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Set tbl = Me.ListObjects("Table2")
Set rng = Intersect(tbl.ListColumns("Balance").DataBodyRange, Target)
If Not rng Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
rng.Offset(0, 3).Value = Now
Application.EnableEvents = True
Yes, they are. I am that fancy.
P.S. I decided to make Table2 align with Table1 so I can extend Table1 and just have the one VBA that I know works in there.
Are your tables really named Table1 and Table2?
HansVogelaar Hmm, no dice. Here is the code modified accordingly. Thoughts?
Private Sub Worksheet_Change(ByVal Target As Range) Dim tbl As ListObject Dim rng As Range Set tbl = Me.ListObjects("Table1") Set rng = Intersect(tbl.ListColumns("Balance").DataBodyRange, Target) If Not rng Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False rng.Offset(0, 3).Value = Now Application.EnableEvents = True Application.ScreenUpdating = True End If Set tbl = Me.ListObjects("Table2") Set rng = Intersect(tbl.ListColumns("Statement Date").DataBodyRange, Target) If Not rng Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False rng.Offset(0, 3).Value = Now Application.EnableEvents = True Application.ScreenUpdating = True End If End SubSee if you can modify this to match your setup:
Private Sub Worksheet_Change(ByVal Target As Range) Dim tbl As ListObject Dim rng As Range Set tbl = Me.ListObjects("Table1") Set rng = Intersect(tbl.ListColumns("Balance").DataBodyRange, Target) If Not rng Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False rng.Offset(0, 3).Value = Now Application.EnableEvents = True Application.ScreenUpdating = True End If ' *** Change the table name as needed *** Set tbl = Me.ListObjects("Table2") ' *** Change the column name as needed *** Set rng = Intersect(tbl.ListColumns("ColumnName").DataBodyRange, Target) If Not rng Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False ' *** Change the column offset 3 as needed *** rng.Offset(0, 3).Value = Now Application.EnableEvents = True Application.ScreenUpdating = True End If End Subpeiyezhu as written, that did not work and I had tried other variations of the same thing with the obvious change of Table1 to Table2. I receive multiple errors with a variety of edits, including the one proposed here. If I put it after "End Sub" it's a problem. If I remove "End If" and "End Sub" and insert it right after the end of the last set of instructions I get "Block If without End If."
