Trying to Understand Worksheet_Change Event

[JMay]

From all my (12) excel books I'm unable to clearly
grasp this idea of how one needs to include the

Application.EnableEvents = False code line at times when
an infinite-loop is triggered...

Below I've set up the following example (trying to better understand):

Private Sub Worksheet_Change(ByVal Target As Range)
Range("D4").Value = Range("D1") + 100 'Cell D1 is formula =B7
If Range("D4").Value > 1000 Then
MsgBox "Your entry produces an amt greater than 1000"
Range("D4").Value = 999
End If
End Sub

While view all 3 windows (the Code window, the spreadsheet and the
immediate)
In the immediate-window I enter:

Range("B7").Value = 2800 << And press enter

The problem? There doesn't seem to be one. LOL

Can someone show me by telling me what I need to
change here in order to get the infinite-loop
that is warned about (where the EnableEvents = False
nullifys the problem)..

TIA,
JMay

 

[Guest]

You are probably only activating the thing twice. The first time because
"something" changed. The second time because D4 changed. From then on you
may be setting D4's value, but not really changing it.

 

[Bob Phillips]

TRy this Jim

Private Sub Worksheet_Change(ByVal Target As Range)
Static nCount As Long
nCount = nCount + 1
Debug.Print nCount
Range("D4").Value = Range("D1") + 100 'Cell D1 is formula =B7
If Range("D4").Value > 1000 Then
MsgBox "Your entry produces an amt greater than 1000"
Range("D4").Value = 999
End If
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JMay]

Bob, thanks...
I see where line 3 <<once it runs>> goes back to the Macro Header
and then steps through and prints another seq number in the immediate
window, but I'm still not seeing the significance. Where is the
infinite
loop taking place?
Jim

 

[Bob Phillips]

It isn't infinite, but in my test it ran 218 times (YES two hundred and
eighteen). Disabling events will stop the extra 217 runs.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JMay]

Thanks Bob - I did learn from this exercise;
I thought (before) that ALL such *changes*
caused an infinite loop ending with a R/T
CRASH !! - But obviously, not so. Thanks
for your help.
Jim May