EnableEvent On Change

[Jim May]

It's obvious i haven't understood this situation where a change, causes a
change, which causes a change -- of course this is happening due to my
line:
Target.Value = Target.Value * 1.06
If I enter 100 in cell D6 - the system goes wild and produces 102,461.64
versus my expected 106.
Help with this would be appreciated.
TIA,

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D5:G10")) Is Nothing Then
Exit Sub
Else
Target.Value = Target.Value * 1.06
End If
End Sub

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto ws_err
Application.EnableEvents = False
If Intersect(Target, Range("D5:G10")) Is Nothing Then
Exit Sub
Else
Target.Value = Target.Value * 1.06
End If

ws_err:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob R.]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D5:G10")) Is Nothing Then
Exit Sub
Application.EnableEvents = False
Else
Target.Value = Target.Value * 1.06
End If
Application.EnableEvents = True
End Sub

 

[Jim May]

Bob:
Thanks;
What logic am I missing here? After EVERY - CHANGE this Macros runs, right?
So what stops it dead (from further calculating) when I enter 100 in cell
D6?
Jim

 

[Guest]

Bob, with all due respect, if you enter a number in a1, for example, your
event procedure leaves Excel's events disabled.

Jim, the following may be a little defensive but should do the trick:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range, cl As Range
If Intersect(Target, Range("D5:G10")) Is Nothing Then Exit Sub
Set myRng = Intersect(Target, Range("D5:G10"))
With Application
.ScreenUpdating = False: .EnableEvents = False
End With
For Each cl In myRng
If IsNumeric(cl.Value) Then cl.Value = cl.Value * 1.06
Next
With Application
.ScreenUpdating = True: .EnableEvents = True
End With
Set myRng = Nothing
End Sub

This will allow for copying and pasting blocks of cells which may or may not
cross into your range, and may or may not contain numeric data.

Regards,
Nate Oliver

 

[Tom Ogilvy]

Events got disabled because of a slight logic problem.

To start them run this macro:

Sub StartEvents()
Appliction.EnableEvents = True
End Sub

fix the code to look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto ws_err
Application.EnableEvents = False
If Not Intersect(Target, Range("D5:G10")) Is Nothing Then
Target.Value = Target.Value * 1.06
End If
ws_err:
Application.EnableEvents = True
End Sub

 

[Jim May]

Tom, thanks for the revised code.
If there is an error then the code jumps to the
ws_err: code line and proceeds to the very next line
Application.EnableEvents = TRUE << which is an (OK) redundent
<< statement
setting the EnableEvents
<< to True
before ending the code.
But if there is an error (Prompted for in line 2 of code)
then all the remaining lines 3 thru the last line of the code runs, right?
when it gets to the line ws_err: << it just reads it and proceeds to the
next line
Application.EnableEvents = TRUE

For the above reason I don't see the illogic of Bob's modified code as it
seems
to do the same thing.

Could you speak to my
So what stops it dead (from further repeating/calculating) when I enter 100
in cell

 

[Guest]

Jim,

You have an error in the line:
If Intersect(Target, Range("D5:G10")) Is Nothing Then

Since you are changing the cell D6 I assume that you want the code triggered
for all cells INSIDE of the range D5:G10, not cells outside this range.

Then Intersect of Target (your cell D6) and range D5:G10 is where they come
together: only cell D6

Now to the point as only Tom got right: If this is NOT....NOTHING then ...do
something. Hence you need to include the word "Not":
If Not Intersect(Target, Range("D5:G10")) Is Nothing Then
With this line, the following will execute if you change any cell in D5:G10

You are changing D6 AGAIN with:
Target.Value= Target.Value*1.06

So, in order to stop the continuous loop, you have to temporarily stop
events from being triggered with:
Application.EnableEvents=False

Unlike Application.ScreenUpdating, EnableEvents does not automatically
reset at the end of a macro. Once turned off it stays off until explicitly
turned back on with EnableEvents = True.

Hope this helps

 

[Bob Phillips]

Jim,

The problem was that your original code exited the sub if the changed cell
was not within your target range. Unfortunately I was concentrating on the
enableevents and ignored this, and so when I added the enabling/disabling
code, it still exit the sub if not match. This bypassed the resetting of the
enableevents.

What Tom did was to invert the test, thereby continuing if it matched, and
remove the exit sub, thereby dropping through to the ws_err: and the
enabling events even if it didn't match.

Sorry about my clumsiness, but being a great believer in serendipity, you
probably learnt more from this discourse than a straight correction .

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim May]

gocush,
thank you very much for the "in-depth" explanation.
I intend to study it over - soon.
Jim

 

[Jim May]

Bob,
Thanks

"my clumsiness"
no way, I just assume you are a mortal, right?
Thanks for your ongoing help.
Jim

 

[Jim May]

I'm always pleased when I learn "A Greater Rule", that is, one that comes
before (through the Order-in-the-Universe) and explains why I have or had
another/Current or subsequent problem. Although I'm still working on which
came first the chicken, or the egg... vbg

I now better understand why it is necessary to do the Enableevents = False
and Subsequent EE=True.

line-by line application:
1) when a change (hand-entry) takes place in the worksheet the Change event
fires.
because Enableevents is ON.
2) If any type error occurs, then basically the code stops (jumps to
ws_err).
3) If no error The Events-Power-Grid is Turned OFF (so no events will be
called).
4) If I change a cell in the D5:G10 range (Which meets the True Condition)
then the Next line is run.
5) The target cell (say F9) is instantly converted from its buffer value
(say 100) to (1+.06) times itself.
6)** The spreadsheet cell F9 now equals 106 (still with the Power-Off).
7) The End If concludes the If Stat
8) The ws_err is read and continues to the next line
9) EnableEvents is Turned Back on so that FUTURE Events can be recognized.
10) End of procedure

So after this I see clearly that my value 106 is finalized (dead as I called
it earlier) and
not subject to the endless looping that can take place without disabling the
events.

I'm only going through this explanation hoping that someone else who is
having trouble
"seeing/comprehending" this can by my experience better understand.

Thanks gocush And Bob for helping me along.