VBA Module Run on Change "Method 'Value' of object 'Range' failed"

When you use Change event code to change the cell(s), you need to set EnableEvents property to False/True in order to avoid unnecessary action.

Please Login or Register  to view this content.

Thank you very much jindon! The Application.EnableEvents works like magic.

I have a follow up question to this. So do you have any advice for newbie like me on how to know what property to add (like in the Change event code to add EnableEvents property)? In the tutorial I followed, the person didn't have EnableEvents and he was able to have the code run. Just want to learn from your experience how to spot missing property or like know best practices? Thank you again for your time!

Originally Posted by datanalyst1

In the tutorial I followed, the person didn't have EnableEvents and he was able to have the code run.

The same code?

If the code doesn't change any cell in the sheet in which the code is written, no need to touch EnableEvents property.
But if the code changes any cell (like your case), Change event will run each time it changes the cell, that means if the code changes 100 cells, it runs 100 times more.

Anyway EnableEvents property is the first to know in the Sheet Event code to avoid unnecessary operation...