Auto time stamp script not working

0

I found this scrtipt https://www.youtube.com/watch?v=gIPdx4-rqGg and after downloading the file, updated ranges, and it worked....then I messed it up.

I tried commenting out the update time stamp, which "broke" something. After that, I deleted script and re-pasted it thinking it would fix the issue - it did not. 

Any ideas of where/what I should look at??

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("E6:E11000")

'Check if the changed cell is in the data tabe or not.
If Intersect(Target, myTableRange) Is Nothing Then Exit Sub

'Stop events from running
Application.EnableEvents = False

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

'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

'Turn events back on
Application.EnableEvents = True
End Sub

Answer
Discuss

Discussion

Now that the script is working, why won't my 'suammary tab' countif() the VBA-entered date is the same as my table? 

I tried referencing the VBA-entered date in an adjacent cell, but it doesn't seem to count unless I "hard code the date."
dmj120 (rep: 2) Jun 7, '21 at 4:39 pm
DMJ
Glad my guess worked and thanks for selecting my Answer.

Your original question didn't include a file so I can't see your summary tab or your COUNTIF formula. 

It's getting late here so I suggest you ask a new question (attaching your working file) and hopefully a contributor from the USA will answer.
John_Ru (rep: 2147) Jun 7, '21 at 4:59 pm
Add to Discussion

Answers

0
Selected Answer

DMJ

If you pasted the macro from the tutorial correctly, it should work if you change a cell in the range UNLESS application events are disabled (in which case workbook or worksheet events won't trigger the associated macros). This can happen if a macro fails midway through. 

Go to the Intermediate window in VB Explorer and paste this:

Application.EnableEvents= True 
then press Return. Go back to the Worksheets and change a cell in the range- if the timestamp appears then you've fixed the "break".

You'll notice that this line appears at the end of the macro. That's because it has to be set the FALSE to prevent the same macro being triggered when it adds the timestamp. Make sense? 

Discuss


Answer the Question

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