Worksheet Change Event Triggering Multiple Times

[PosseJohn]

A have a Worksheet_Change event set to take action if specific cells are
modified.

The routine validates the change, and then performs a calculation, and
places the answer in another cell.

After the event triggers, the action DESIRED is seen within the first time
the event occurs, but the event KEEPS REPEATING for some reason. I've
determined the event repeats as much as 1,700 times.

Has anyone experienced this before? How can I stop it from occuring?

Thanx!

 

[Mike H]

Hi,

Changing cells calls the event again so you must disable events while doing
this

application.enableevents=false
'your code
application.enableevents=true

Mike

 

[Chip Pearson]

If your code in the Change event procedure changes some cell value,
that will cause the Change event to run again. And it that iteration
changes a cell, Change will run again and again, until it blows up
with an "Out Of Stack Space" error.

Use code like the following to temporarily disable events and prevent
Change from calling itself.

Application.EnableEvents = False
' your code here
Application.EnabeEvents = True

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)