Timestamp vba

[RPM7]

I've got a very basic understanding of VBA & I'm trying to create a spreadsheet with a timestamp along with the username when a cell within a range is modified.

I've have found loads of examples of vba with different variations that're pretty close, but I can't seem to make them work for me.
The closet I've found will add a username and timestamp when cells in a singular column are changed. It offsets the stamp from this target range.

I have information in ranges A2:L34 & A40:L82, & I was hoping to create a stamp in column O & P with the username and time modified respectively.

Does anyone know how to do this?

Any help at all would be greatly appreciated as I'm going in circles.

 

[Peter_SSs]

See if this does what you want.
If not, please provide details.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, Rw As Range

  Set Changed = Intersect(Target, Union(Range("A2:L34"), Range("A40:L82")))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each Rw In Changed.Rows
      Cells(Rw.Row, "O").Value = Environ("Username")
      Cells(Rw.Row, "P").Value = Now
    Next Rw
    Application.EnableEvents = True
  End If
End Sub

 

[RPM7]

That worked perfectly Peter.

Thanks for your fast response.

 

[Peter_SSs]

That worked perfectly Peter.

Thanks for your fast response.

Glad to help. Thanks for letting us know it worked.

 

[OnTheRange]

Peter, that works great. One thing though, is there a way to modify the code so it doesn't stamp when the contents of the cells are cleared?

 

[OnTheRange]

Anyone have any ideas?

 

[Peter_SSs]

Peter, that works great. One thing though, is there a way to modify the code so it doesn't stamp when the contents of the cells are cleared?

That code was written for where the target was a block of cells, not necessarily all in one column. I'm guessing that is not your circumstance.

- If it is your circumstance, what would you want to happen if 2 cells were pasted into a row and one of those cells contained data (requiring a timestamp) and the other cell in the same row was blank (requiring no timestamp)?

- If it is not your circumstance, tell us more about your specific layout & requirements.

 

[OnTheRange]

That code was written for where the target was a block of cells, not necessarily all in one column. I'm guessing that is not your circumstance.

- If it is your circumstance, what would you want to happen if 2 cells were pasted into a row and one of those cells contained data (requiring a timestamp) and the other cell in the same row was blank (requiring no timestamp)?

- If it is not your circumstance, tell us more about your specific layout & requirements.

I'm only looking at a certain range, not the whole column. For example, if data was entered anywhere in range a1:a10, then there would be a timestamp in the same row of column G. If data was pasted into both column a and b of the same row, then column G would still be the only column to received a timestamp. Also, when the contents of a cell in range a1:a10 is cleared, it would not erase the timestamp.

 

[Peter_SSs]

I'm only looking at a certain range, not the whole column. For example, if data was entered anywhere in range a1:a10, then there would be a timestamp in the same row of column G. If data was pasted into both column a and b of the same row, then column G would still be the only column to received a timestamp. Also, when the contents of a cell in range a1:a10 is cleared, it would not erase the timestamp.

OK, try this.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Const myTarget As String = "A1:A10"

  Set Changed = Intersect(Target, Range(myTarget))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) Then
        Cells(c.Row, "G").Value = Now
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[OnTheRange]

That works great, thanks!