Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to Understand Worksheet_Change Event
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to Understand Worksheet_Change Event
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. -- Gary's Student gsnu200709 "JMay" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to Understand Worksheet_Change Event
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to Understand Worksheet_Change Event
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" wrote in message : 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to Understand Worksheet_Change Event
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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to Understand Worksheet_Change Event
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 "Bob Phillips" wrote in message : 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" wrote in message ... 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" wrote in message : 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change event | Excel Programming | |||
Worksheet_change event. | Excel Programming | |||
Worksheet_change event | Excel Programming | |||
Worksheet_Change Event | Excel Programming | |||
Worksheet_Change Event | Excel Programming |