data validation not working while we paste the values (for removing duplicates in a column

If it is just a matter of preserving the formatting then

ctrl+V then tap ctrl then v

But I imagine you don't want that because it side steps the data validation.
If that is not what you want then what would you want/expect it to do when you ask it to paste a set of data that breaks the data validation rules?

Even I use copy & paste , then also data validation in cells has to work.

OK I put my idea in a sheet.

Validation.xlsm.

If you paste you should use the paste special of course.

Two sheets one with buttons and one with a macro that runs on change.

Hi,

I didn't understand how did you wrote that Macro. Please explain the process.

I am not sure what you mean.

Are you saying it is useful and want to know how to change it, or that you can't use it so you don't know ... or something else?

You may want to provide more information (like a real example in use) to make sure we are on the same page if you want to get much further.

Or you can consider the advise of the others here that data validation where people are pasting into the area might be the wrong solution. Maybe stepping back might give another more simple solution.

Hi

My question is very clear. Previously you send one attachment (Validation.xlsm), in that you wrote a macro using controls. I want to know that process (macro coding and overall process).

thanks in advance.

Validation.xlsm

Well I hope this helps.
Almost every row of code commented and instructions on sheet.

The Macros are in the sheet2 object(the on change macro) and the 2 modules.

I just left the on change version of the macro to make it clear, and I added a reset to the validation so users don't need paste special (to make the instructions simple).

But I didn't comment that one as it was just a straight recording of the action using a macro recorder.