Timestamp on worksheet change

[nparsons75]

Hi,

I have two worksheets

1. sheet 1
2. sheet 2

In sheet 1 I a have two cells that I would like to autofil with username and timestamp when data is changed on sheet 2.

A1 = username
A2 = timestamp


If anything changes on sheet 2 I need A1 and A2 to have the username and timestamp automatically added. The aim is to see when sheet 2 was last updated.

Thank you in advance.

 

[Peter_SSs]

Not sure I'm entirely clear on the requirement but see if this helps. It will record when any change is made within the range you indicated.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("V"), Rows("4:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      c.Offset(, 3).Value = Environ("username")
      c.Offset(, 4).Value = Now
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[MrRosco]

Not sure I'm entirely clear on the requirement but see if this helps. It will record when any change is made within the range you indicated.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("V"), Rows("4:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      c.Offset(, 3).Value = Environ("username")
      c.Offset(, 4).Value = Now
    Next c
    Application.EnableEvents = True
  End If
End Sub

Thanks for this, i will give it a try and report back any issues. once again thank you for all your help so far
Ross

 

[MrRosco]

Not sure I'm entirely clear on the requirement but see if this helps. It will record when any change is made within the range you indicated.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("V"), Rows("4:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      c.Offset(, 3).Value = Environ("username")
      c.Offset(, 4).Value = Now
    Next c
    Application.EnableEvents = True
  End If
End Sub

Hi Peter,

i have tested and it works, so thank you for that. i do have one question, can the code be tweaked so that if column V has Active selected can Y,Z be left blank?

cheers
Ross

 

[Peter_SSs]

Try changing the middle part of the code to this

For Each c In Changed
  Select Case c.Value
    Case "Won", "Lost"
      c.Offset(, 3).Value = Environ("username")
      c.Offset(, 4).Value = Now
    Case Else
      c.Offset(, 3).Resize(, 2).ClearContents
  End Select
Next c

 

[SiRhOSEven]

Hi Peter, suppose I have data in column B and I want to timestamp in Column A when data changes on Column B, how can I achieve that?

Thank you for your usual support.