Worksheet Change Event Triggering Multiple Times

P

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

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
 
C

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Simple Copy Paste within Worksheet Change Event 2
Include Dropdowns and Checkboxes in Worksheet Change event 1
MultiWorkbook Change Event Detection / Spell Checker 4
Worksheet Change Event macro fails intermittently 6
Validation not triggering Change event in Excel XP 3
Worksheet Change Event 4
Calculation of cell value with worksheet change event 2
Cell Change triggers Worksheet Event 13

Top