worksheet_Change code changes a cell, so why not repeats?

[John Kauffman]

Theoretical question. Following works, but I wonder why.

Worksheet_Change()
Cells(Target.Row,3) = NOW()
end sub

My Confusion - I would predict it would not work because if a user types "x" into A1 then the event fires and C1 gets the date. But why doesn't the change in C1 fire another Worksheet_Change and go into an endless loop of changing C1?

Thanks.

 

[mikerickson]

It does.

Consider:

User types "x" into A1, which changes the value in A1 and the Change event fires
Code puts NOW into C1, which changes the value of C1 and the Change event fires
Code puts NOW into C1, which does NOT change the value in C1, so no Change event.

You can see this if you add one line

Private Sub Worksheet_Change()
    Cells(Target.Row,3) = NOW()
    MsgBox "Change"
End sub

Typing "x" into A1 should cause two message boxes to appear.

To avoid cascading event procedures you can use Application.EnableEvents = False (and True at the end of the routine).

 

[AlphaFrog]

I ran this as a test in Excel 2003. It seems to create a cascading event 225 times and then stops.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Static[/color] counter
    Cells(Target.Row, 3) = Now
    counter = counter + 1
    Debug.Print counter
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[mikerickson]

When I run that in 2011, I get either 1 or 2.
How finely tuned is Windows NOW function?
What portion of a second does it return?
On my Mac, it only returns to the whole second.

 

[AlphaFrog]

I changed the NOW to a fixed value of 1 and it still cascaded 225 times. Is there a setting somewhere that affects this?

 

[mikerickson]

Hmm...
This code does not trigger a change event in 2011.

Sub test()
    With Range("a10")
        .Value = .Value
    End With
End Sub

But if you enter a cell, put the cursor in the Formula edit box, change nothing and press Return, a Change event is triggered.

It may be what Windows/Mac considers a change.

 

[AlphaFrog]

Double Hmm...

In Excel 2003, your test macro does trigger a change event and it cascaded 223 times. Interesting.

Thanks mikerickson. I was just curious on how this all works. You helped shed some light on it.

 

[Norie]

AlphaFrog

I tried almost the exact same test, apart from the variable name.

First time I 'checked' the last number in the Immediate Window was 95.

Tried it again it was up to 224.

Then I left it for a bit, came back and when I tried to check Excel had stopped responding.

I also tried it with Time rather than Now().

 

[John Kauffman]

Thanks, guys. Theory helped me.
For future searchers, this works fine (Win / Excel 2010)

Private Sub Worksheet_Change(ByVal Target As Range)
'MsgBox "start"
' When there is a change in a row:
' puts a time / date / user stamp in same row, cols c:E
Application.EnableEvents = False
Cells(Target.Row, 3) = Format(Now(), "D")
Cells(Target.Row, 4) = Format(Now(), "hh:mm:ss")
Cells(Target.Row, 5) = Application.UserName
Application.EnableEvents = True
End Sub