Data validation runs worksheet event twice

[jaryszek]

Hi,

i am using DV list:
Screenshot_16.png

and i have also worksheet change event:

Please Login or Register  to view this content.

This is ok.

But when you will input into B2 number for example 5 and DV list will show error, worksheet change event will go twice.
Once for your try to input 5 and second time for returning back (in this case to allowed by DV number 2) after canceling new input.

How can i solve the issue?
Please help,
Jacek

[Pepe Le Mokko]

Hi
as not everybody is supposed to understand acronyms, it is best to be more explicit in your thread title.
I changed the DV to "data validation" for you in your thread title.

[jaryszek]

Thank you Pepe,

Jacek

[Daishiknyte]

Any change to a worksheet triggers the Worksheet_Change event. This means your code runs the first time when you change the cell, then runs again when the code changes the cell. To prevent this, you need to disable worksheet events at the beginning of your code, then enable events after your code runs.

Please Login or Register  to view this content.

[jaryszek]

Thank you Daishiknyte,

ok but it has to be workaround for this.
Why to loop twice if DV is wrong and throwing error?

Maybe i should add function to check if data is correct for DV?

Best,
Jacek

[jaryszek]

Anyone?

Jacek

[Pepe Le Mokko]

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

[kvsrinivasamurthy]

This code pop up msg box only when the value of B2 is changed. If there is no change in value no popup.
Code for worksheet event

Please Login or Register  to view this content.

[jaryszek]

o wow great code thank you very much!

Jacek

[kvsrinivasamurthy]

Thanks for feed back.