Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Troubleshooting My Auto-Timestamp Macro

0

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
'   TeachExcel.com
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.
Answer
Discuss

Answers

0
Selected Answer

I knew it would be something small lol.

Problem line:

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

Solution:

'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.

Discuss


Answer the Question

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