Worksheet change does not happen

[dkirk500]

Guys ,

I used this simple code to track the changes made in worksheet( sheet7) using timestamp. The code only works if i change the cell value within sheet 7. However i have a data entry form on sheet6 which data goes to sheet 7. If i make any changes from sheet6 , timestamp does not happen. There are no issues of data changes between sheet 6 and sheet 7. What am I doing wrong here?

Private Sub Worksheet_Change(ByVal Target As Range)


Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range


Set myTableRange = Sheet7.Range("A4:r9999")

If Intersect(Target, myTableRange) Is Nothing Then Exit Sub
Application.EnableEvents = False

Set myDateTimeRange = Range("v" & Target.Row)
Set myUpdatedRange = Range("w" & Target.Row)

If myDateTimeRange.Value = "" Then
    myDateTimeRange.Value = Now
End If

myUpdatedRange.Value = Now

Application.EnableEvents = True
End Sub

 

[dkirk500]

Indeed its not. . On a fresh workbook i used two sheets (1 &2) to replicate the problem. On sheet1 cell A1 , i just put a test value. On sheet 2 , cell A1, i used =sheet1!A1 to copy the data ( there wasn't any code) and used the worksheet change code to see if it works. It does not like i stated above However it works when i directly change the value in the sheet 2 range (specified on worksheet change code), it works. Hope it explains.

 

[RoryA]

I already said at the start that formulas do not trigger worksheet_change

 

[dkirk500]

Thanks. I think that was the issue. I just made one more test to confirm the theory to directly put formula on sheet2, it did not work. I was in an impression that simple copying is not part of the formula. . I have to put it on calculate events then?

 

[dkirk500]

I apologise , I responded the earlier message too fast ( i choose the the wrong range to run the test as my code range is A4:R9999, so obviously when i used a formula in A1, it did not work. The formula actually triggers the change on sheet 2. If you have few mins, you could replicate the issue and see it yourself. I sincerely appreciate your times.

 

[RoryA]

Entering a formula initially will trigger a change event. If the result of that formula subsequently changes, that will not trigger a change event. It is not clear to me exactly what you are doing to each sheet currently.

 

[dkirk500]

If so, do i have to put it under worksheet calculate event?