Stop Capture Event

[OverAC]

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

 

[Bob Phillips]

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)

 

[MattShoreson]

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.

 

[OverAC]

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

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...