Dears,

Sometimes I work with worksheet event to capture change event, but
right after that event I change some other things in this sheet but I
dont want worksheet_Change event procedure run like a recursion. So I
want VBA to capture this event. How can I do this?

Thanks for any reply

OverAC

-------
English is not my mother tounge, hope that you can understand what I
want to explain


--
OverAC
------------------------------------------------------------------------
OverAC's Profile: http://www.excelforum.com/member.php...o&userid=32396
View this thread: http://www.excelforum.com/showthread...hreadid=531826

Test the cell being changed

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"OverAC" wrote in
message ...

Dears,

Sometimes I work with worksheet event to capture change event, but
right after that event I change some other things in this sheet but I
dont want worksheet_Change event procedure run like a recursion. So I
want VBA to capture this event. How can I do this?

Thanks for any reply

OverAC

-------
English is not my mother tounge, hope that you can understand what I
want to explain


--
OverAC
------------------------------------------------------------------------
OverAC's Profile:
http://www.excelforum.com/member.php...o&userid=32396
View this thread: http://www.excelforum.com/showthread...hreadid=531826

There may be some neater way of soving the problem, but I use a boolean
flag to check to see if the event has been triggered, depending on your
code it may need to be a public variable.

So on the first trigger of the change event set a flag
(e.g.blnfirstTime) at the end of the proc to true.

when the event is triggered again, evaluate the flag at the beginning
of the procedure and finish the sub neatly if needed.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=531826

Code:
--------------------
Application.EnableEvents = False
Application.EnableEvents = True
--------------------

Thanks Bob, that's so great. That's all I need

Dear MattShoreson,

I used to code like what you said. But from now on It will change.

Thank you all.

OverAC


--
OverAC
------------------------------------------------------------------------
OverAC's Profile: http://www.excelforum.com/member.php...o&userid=32396
View this thread: http://www.excelforum.com/showthread...hreadid=531826

Matt,

Excel provides its own event flag that you can use. See the example that I
posted.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"MattShoreson"
wrote in message
news:MattShoreson.263hiy_1144750503.4506@excelforu m-nospam.com...

There may be some neater way of soving the problem, but I use a boolean
flag to check to see if the event has been triggered, depending on your
code it may need to be a public variable.

So on the first trigger of the change event set a flag
(e.g.blnfirstTime) at the end of the proc to true.

when the event is triggered again, evaluate the flag at the beginning
of the procedure and finish the sub neatly if needed.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=531826