worksheet change event

this poiint

I shall follow your advice to place a breakpoint in the code at the first
line and check with step triggering the macro .
thanks once again for removing c
thank you very much for the detailed explanation .
now I appear to understand.
That is why I am little scared whenever I use the event codes lest it may
give some unexpected results even though I have used quite a lot

I used to wonder that in the event
Mr. Kiosterud

codes given by experts like you, you always use a statement in the beginning
application.enableevents=false
and then at the end a TRUE statement.
Now some insight is coming in onobwebs in understnading vba and event codes.

venkat

"Earl Kiosterud" <someone@nowhere.com> wrote in message
news:u1jbhG2vGHA.1956@TK2MSFTNGP02.phx.gbl...
> R.,
>
> When your routine increments B2, the change is retriggering the routine.
> Since A1 is still 1000, B2 gets bumped again. By all rights, it should
> have incremented B2 until it blew a fuse, long past 222.
>
> Try this:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Value = 1000 And Target.Address = "$A$1" Then
> Application.EnableEvents = False ' prevent next statement from
> retriggering this event sub
> Range("B2") = Range("B2") + 1
> Application.EnableEvents = True
> End If
> End Sub
>
> Target is whatever cell was changed. The EnableEvents = False is
> necessary to prevent the change of B2 from triggering the routine. That
> wouldn't cause a problem here, because in the second entry, Target isn't
> A1, so it'll just fall through, then finish the first path through the
> routine. But we disable events anyway, because we like to do things
> right.
>
> You might want to put a break in an early line (put the cursor there,
> press F9). Then when you change a cell and the routine is entered, you
> can single-step it from there (F8) to see what it's doing.
>
> The second line is more typically written like this:
>
> If Target.Value = 1000 And Not Intersect(Target, Range("A1")) Is Nothing
> Then
> ...
>
> That oughtta give you pause.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "R..VENKATARAMAN" <$$$$venkat1926@$$$yahoo.com> wrote in message
> news:OQoqcs0vGHA.4444@TK2MSFTNGP05.phx.gbl...
>>I intitialise B2 as 0(zero)
>> then my event code is like this
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Set Target = Range("a1")
>> If Target = 1000 Then Range("B2") = Range("B2") + 1
>> End Sub
>>
>> now if I type 1 B2 remains 0
>> then If type 1000 B2 become 222
>> what is this 222?
>>
>> I changed the event code by removing the line
>> set target=range("a1")
>> then it is ok i.e. whenever 1000 is entered in A1, B2 increments by 1.
>> but the snag is if I change some other cell to 1000 then also B2
>> increments by anoher 1.
>>
>> some elucidation will be helpful. I agree there is some confusion in my
>> mind reg worksheet change code and also the impliatin of term "target"
>>
>> Kind regards.
>>
>> excel 2002/XP
>>
>
>

I apologise. some mistake has occured in my computer in sending the reply.
there were incomplete messagea and also they were repeated.
SORRY.

venkat


"R..VENKATARAMAN" <$$$$venkat1926@$$$yahoo.com> wrote in message
news:egsn%23N3vGHA.4384@TK2MSFTNGP04.phx.gbl...
> this poiint
>
> I shall follow your advice to place a breakpoint in the code at the first
> line and check with step triggering the macro .
> thanks once again for removing c
> thank you very much for the detailed explanation .
> now I appear to understand.
> That is why I am little scared whenever I use the event codes lest it may
> give some unexpected results even though I have used quite a lot
>
> I used to wonder that in the event
> Mr. Kiosterud
>
> codes given by experts like you, you always use a statement in the
> beginning
> application.enableevents=false
> and then at the end a TRUE statement.
> Now some insight is coming in onobwebs in understnading vba and event
> codes.
>
> venkat
>
> "Earl Kiosterud" <someone@nowhere.com> wrote in message
> news:u1jbhG2vGHA.1956@TK2MSFTNGP02.phx.gbl...
>> R.,
>>
>> When your routine increments B2, the change is retriggering the routine.
>> Since A1 is still 1000, B2 gets bumped again. By all rights, it should
>> have incremented B2 until it blew a fuse, long past 222.
>>
>> Try this:
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Value = 1000 And Target.Address = "$A$1" Then
>> Application.EnableEvents = False ' prevent next statement from
>> retriggering this event sub
>> Range("B2") = Range("B2") + 1
>> Application.EnableEvents = True
>> End If
>> End Sub
>>
>> Target is whatever cell was changed. The EnableEvents = False is
>> necessary to prevent the change of B2 from triggering the routine. That
>> wouldn't cause a problem here, because in the second entry, Target isn't
>> A1, so it'll just fall through, then finish the first path through the
>> routine. But we disable events anyway, because we like to do things
>> right.
>>
>> You might want to put a break in an early line (put the cursor there,
>> press F9). Then when you change a cell and the routine is entered, you
>> can single-step it from there (F8) to see what it's doing.
>>
>> The second line is more typically written like this:
>>
>> If Target.Value = 1000 And Not Intersect(Target, Range("A1")) Is Nothing
>> Then
>> ...
>>
>> That oughtta give you pause.
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>>
>> "R..VENKATARAMAN" <$$$$venkat1926@$$$yahoo.com> wrote in message
>> news:OQoqcs0vGHA.4444@TK2MSFTNGP05.phx.gbl...
>>>I intitialise B2 as 0(zero)
>>> then my event code is like this
>>>
>>> Private Sub Worksheet_Change(ByVal Target As Range)
>>> Set Target = Range("a1")
>>> If Target = 1000 Then Range("B2") = Range("B2") + 1
>>> End Sub
>>>
>>> now if I type 1 B2 remains 0
>>> then If type 1000 B2 become 222
>>> what is this 222?
>>>
>>> I changed the event code by removing the line
>>> set target=range("a1")
>>> then it is ok i.e. whenever 1000 is entered in A1, B2 increments by 1.
>>> but the snag is if I change some other cell to 1000 then also B2
>>> increments by anoher 1.
>>>
>>> some elucidation will be helpful. I agree there is some confusion in my
>>> mind reg worksheet change code and also the impliatin of term "target"
>>>
>>> Kind regards.
>>>
>>> excel 2002/XP
>>>
>>
>>
>
>
>