VBA Code Timestamp & Username Copy & Paste Method

[OliverTheLapponian]

Good day all,

I have the following VBA code below to help show when a change was made in one of the columns in my Excel spreadsheet as well as the username however, I am finding difficulties when I am testing my VBA code. I can only retrieve the username/time stamp if I double click on each individual cell and make edits on a individual cell. This is problematic as I will only receive the username/timestamp on the first pasted cell within a range of cells. Any assistance is greatly appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)



Application.EnableEvents = False

If Target.Row > 1 Then

Cells(Target.Row, "O") = Format(Now, "mm/dd/yyyy HH:mm:ss")

Cells(Target.Row, "P") = Environ("username")

End If

Application.EnableEvents = True

End Sub

 

[bbrnx19]

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Row > 1 Then

Range("O" & Target.Row).Resize(Target.Count, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")

Range("P" & Target.Row).Resize(Target.Count, 1).Value = Environ("username")

End If

Application.EnableEvents = True

End Sub

 

[My Aswer Is This]

Your script says Target row must be greater then 1 but does not specify a particular column

 

[bbrnx19]

post #2 replace

Target.Count

with

UBound(Target.Value2)

 

[OliverTheLapponian]

post #2 replace

with

UBound(Target.Value2)

Thank you so much for your assistance!

 

[OliverTheLapponian]

Thank you so much for your assistance!

With this VBA code I am only able to see the username/timestamp with the copy and paste method, would you please be able to assist so that an individual cell is clicked there is a timestamp and if the cells are copy and pasted there is a username/timestamp. Thank you!

 

[OliverTheLapponian]

Thank you so much for your assistance!

Thank you so much for your assistance!

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Row > 1 Then

Range("O" & Target.Row).Resize(Target.Count, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")

Range("P" & Target.Row).Resize(Target.Count, 1).Value = Environ("username")

End If

Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Row > 1 Then

Range("O" & Target.Row).Resize(Target.Count, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")

Range("P" & Target.Row).Resize(Target.Count, 1).Value = Environ("username")

End If

Application.EnableEvents = True

End Sub

With this VBA code I am only able to see the username/timestamp with the copy and paste method, would you please be able to assist so that an individual cell is clicked there is a timestamp and if the cells are copy and pasted there is a username/timestamp. Thank you!

 

[bbrnx19]

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If IsArray(Target.Value) Then
    r = UBound(Target.Value)
Else
    r = 1
End If

If Target.Row > 1 Then

Range("O" & Target.Row).Resize(r, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")

Range("P" & Target.Row).Resize(r, 1).Value = Environ("username")

End If

Application.EnableEvents = True

End Sub

 

[OliverTheLapponian]

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If IsArray(Target.Value) Then
    r = UBound(Target.Value)
Else
    r = 1
End If

If Target.Row > 1 Then

Range("O" & Target.Row).Resize(r, 1).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")

Range("P" & Target.Row).Resize(r, 1).Value = Environ("username")

End If

Application.EnableEvents = True

End Sub

Works perfect. Thank you so much!