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

 

[RoryA]

However i have a data entry form on sheet6 which data goes to sheet 7

If you mean that there are formulas on sheet 7 that link to the data on sheet 6, then those will not trigger the Worksheet_Change event. Formula recalculations trigger the Worksheet_Calculate event.

 

[dkirk500]

There are no formulas. Just cell references such as sheet6.range("A5").value = sheet7("A5").value used in command button on sheet 6.

 

[RoryA]

Then that should work unless your other code disables events while it's processing.

 

[dkirk500]

I actually tried to a completely new workbook. Fresh sheet nothing but A1 (sheet1) , and sheet2.A1=sheet1!a1 value and I still get the same problem.

 

[RoryA]

In the Immediate window type ?application.enableevents and press Enter. Does it return False?

 

[dkirk500]

Its true.
?application.EnableEvents
True

 

[RoryA]

Can you post the actual code you have, since what you posted just now is clearly not the real code? Also, just as a long shot, do you have any conditional formatting that uses UDFs?

 

[dkirk500]

It does not have any conditional formatting. I am sorry, which real code you are referring? The code is above the post and below i showed the outcome of ?application.enableevents in immediate browser.

 

[RoryA]

“sheet2.A1=sheet1!a1 value” is not valid code