VBA Code Timestamp & Username Copy & Paste Method

OliverTheLapponian

New Member
Joined
Jan 6, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
VBA Code:
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
 
Upvote 0
Your script says Target row must be greater then 1 but does not specify a particular column
 
Upvote 0
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!
 
Upvote 0
Thank you so much for your assistance! :)
Thank you so much for your assistance! :)
VBA Code:
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
VBA Code:
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!
 
Upvote 0
VBA Code:
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
 
Upvote 0
Solution
VBA Code:
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! :)
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top