EnableEvent On Change

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

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

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)


"Jim May" <jmay@cox.net> wrote in message
news:Sj5Yd.58600$%U2.51909@lakeread01...
> 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:
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

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:e6cXU8cJFHA.616@TK2MSFTNGP10.phx.gbl...
> 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)
>
>
> "Jim May" <jmay@cox.net> wrote in message
> news:Sj5Yd.58600$%U2.51909@lakeread01...
> > 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, 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

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


--
Regards,
Tom Ogilvy

"Jim May" <jmay@cox.net> wrote in message
news:RB5Yd.58764$%U2.37993@lakeread01...
> 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
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:e6cXU8cJFHA.616@TK2MSFTNGP10.phx.gbl...
> > 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)
> >
> >
> > "Jim May" <jmay@cox.net> wrote in message
> > news:Sj5Yd.58600$%U2.51909@lakeread01...
> > > 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
> > >
> > >
> >
> >
>
>

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
> > D6?


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uSloo7dJFHA.2576@TK2MSFTNGP15.phx.gbl...
> 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
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim May" <jmay@cox.net> wrote in message
> news:RB5Yd.58764$%U2.37993@lakeread01...
> > 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
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:e6cXU8cJFHA.616@TK2MSFTNGP10.phx.gbl...
> > > 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)
> > >
> > >
> > > "Jim May" <jmay@cox.net> wrote in message
> > > news:Sj5Yd.58600$%U2.51909@lakeread01...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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

"Jim May" wrote:

> 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
> > > D6?
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:uSloo7dJFHA.2576@TK2MSFTNGP15.phx.gbl...
> > 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
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Jim May" <jmay@cox.net> wrote in message
> > news:RB5Yd.58764$%U2.37993@lakeread01...
> > > 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
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:e6cXU8cJFHA.616@TK2MSFTNGP10.phx.gbl...
> > > > 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)
> > > >
> > > >
> > > > "Jim May" <jmay@cox.net> wrote in message
> > > > news:Sj5Yd.58600$%U2.51909@lakeread01...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>

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" <jmay@cox.net> wrote in message
news:588Yd.60860$%U2.41931@lakeread01...
> 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
> > > D6?
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:uSloo7dJFHA.2576@TK2MSFTNGP15.phx.gbl...
> > 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
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Jim May" <jmay@cox.net> wrote in message
> > news:RB5Yd.58764$%U2.37993@lakeread01...
> > > 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
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:e6cXU8cJFHA.616@TK2MSFTNGP10.phx.gbl...
> > > > 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)
> > > >
> > > >
> > > > "Jim May" <jmay@cox.net> wrote in message
> > > > news:Sj5Yd.58600$%U2.51909@lakeread01...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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


"gocush" <gocushNOT_THIS@comcast.net/delete> wrote in message
news:8B5A6CA5-E6FE-47CC-A4AA-E52436D10B92@microsoft.com...
> 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
>
> "Jim May" wrote:
>
> > 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
> > > > D6?
> >
> >
> > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > news:uSloo7dJFHA.2576@TK2MSFTNGP15.phx.gbl...
> > > 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
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Jim May" <jmay@cox.net> wrote in message
> > > news:RB5Yd.58764$%U2.37993@lakeread01...
> > > > 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
> > > >
> > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > > news:e6cXU8cJFHA.616@TK2MSFTNGP10.phx.gbl...
> > > > > 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)
> > > > >
> > > > >
> > > > > "Jim May" <jmay@cox.net> wrote in message
> > > > > news:Sj5Yd.58600$%U2.51909@lakeread01...
> > > > > > 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,
Thanks

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


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23mm6cfiJFHA.1176@TK2MSFTNGP15.phx.gbl...
> 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" <jmay@cox.net> wrote in message
> news:588Yd.60860$%U2.41931@lakeread01...
> > 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
> > > > D6?
> >
> >
> > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > news:uSloo7dJFHA.2576@TK2MSFTNGP15.phx.gbl...
> > > 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
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Jim May" <jmay@cox.net> wrote in message
> > > news:RB5Yd.58764$%U2.37993@lakeread01...
> > > > 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
> > > >
> > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > > news:e6cXU8cJFHA.616@TK2MSFTNGP10.phx.gbl...
> > > > > 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)
> > > > >
> > > > >
> > > > > "Jim May" <jmay@cox.net> wrote in message
> > > > > news:Sj5Yd.58600$%U2.51909@lakeread01...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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.


"Jim May" <jmay@cox.net> wrote in message
news:r1hYd.66252$%U2.14348@lakeread01...
> gocush,
> thank you very much for the "in-depth" explanation.
> I intend to study it over - soon.
> Jim
>
>
> "gocush" <gocushNOT_THIS@comcast.net/delete> wrote in message
> news:8B5A6CA5-E6FE-47CC-A4AA-E52436D10B92@microsoft.com...
> > 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
> >
> > "Jim May" wrote:
> >
> > > 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
> > > > > D6?
> > >
> > >
> > > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > > news:uSloo7dJFHA.2576@TK2MSFTNGP15.phx.gbl...
> > > > 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
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Jim May" <jmay@cox.net> wrote in message
> > > > news:RB5Yd.58764$%U2.37993@lakeread01...
> > > > > 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
> > > > >
> > > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
message
> > > > > news:e6cXU8cJFHA.616@TK2MSFTNGP10.phx.gbl...
> > > > > > 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)
> > > > > >
> > > > > >
> > > > > > "Jim May" <jmay@cox.net> wrote in message
> > > > > > news:Sj5Yd.58600$%U2.51909@lakeread01...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>