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.

 

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

 

[R..VENKATARAMAN]

thank you very much, now I appear to understand.

thanks once again

venkat

 

[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

 

[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

 

[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

 

[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

 

[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