worksheet change event

[R..VENKATARAMAN]

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

[Jim Cone]

"Target" is the cell that has been changed.
Things can cascade on you if you change a cell value in your code.
That causes your code to run again which changes a cell value which makes
your code run again and so on...
What are you trying to do?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"R..VENKATARAMAN"
<$$$$venkat1926@$$$yahoo.com>
wrote in message
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

[R..VENKATARAMAN]

thank you.
my target cell is A1 which changes.
what I want to find is:
I am changing the values in A1. I want to find out how many times I have
entered 1000 in A1. I do not want B2 to increment if I enter 1000 in any
other cell.

I hope I made myself clear.

I am also intrgiued by the no. 222 and whenever I enter 1000 in A1, B2
increments by 222.



"Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
news:eoQruK1vGHA.4880@TK2MSFTNGP04.phx.gbl...
> "Target" is the cell that has been changed.
> Things can cascade on you if you change a cell value in your code.
> That causes your code to run again which changes a cell value which makes
> your code run again and so on...
> What are you trying to do?
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> "R..VENKATARAMAN"
> <$$$$venkat1926@$$$yahoo.com>
> wrote in message
> 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
>
>

[Earl Kiosterud]

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
>

[R..VENKATARAMAN]

thank you very much, now I appear to understand.

thanks once again

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

[R..VENKATARAMAN]

Mr. Kiosterud

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 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 on 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 cobwebs 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
>>
>
>

[R..VENKATARAMAN]

Mr. Kiosterud

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 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 on 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 cobwebs 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
>>
>
>

[R..VENKATARAMAN]

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

[R..VENKATARAMAN]

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

[R..VENKATARAMAN]

Mr. Kiosterud
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 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.

I shall follow your advice to place a breakpoint in the code at the first
line and check with step triggering the macro .

Now some insight is coming in.
thanks once again for removing cobwebs 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
>>
>
>