Troubleshooting My Auto-Timestamp Macro


I have an attachment that I asked a previous question about how to return the user that is editing that Row and place it in Column Q. I also want to return the date and time stamp like in your last video in Row O and P. (My data will be in Cell Range A2:J1500)

The formula that you sent me last is still in the worksheet and worked at first until i messed with some settings to protect the data and now it wont return any of the values. Can you help?

Macro Used:

Private Sub Worksheet_Change(ByVal Target As Range)
'Timestamp Data
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range
'Your data table range
Set myTableRange = Range("A2:J1500")
'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub
'Column for the date/time
Set myDateTimeRange = Range("P" & Target.Row)
'Column for last updated date/time
Set myUpdatedRange = Range(")" & Target.Row)
'Stop events from running
Application.EnableEvents = False
'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then
    myDateTimeRange.Value = Now
End If
'Update the updated date/time value
myUpdatedRange.Value = Now
'Update cell with username - 1 = Column A; 2 = Column B; etc.
Cells(Target.Row, 17).Value = Application.UserName
'Turn events back on
Application.EnableEvents = True
End Sub
Post Edited
Title: Title was not descriptive.
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Tags: Tags were updated to reflect the topic of the question.


Selected Answer

I knew it would be something small lol.

Problem line:

'Column for last updated date/time
Set myUpdatedRange = Range(")" & Target.Row)


'Column for last updated date/time
Set myUpdatedRange = Range("O" & Target.Row)

Somehow, you put ) instead of O for the column reference.

Also, please note that when you post VBA code here, you need to put it inside the tags that appear when you click the CODE button and  the Question title must be descriptive; as this is for a new issue, it shouldn't have the same title as your first question. I'll change both for you now, but please remember this in the future.


Answer the Question

You must create an account to use the forum. Create an Account or Login