Pasting data as values only ignores data validation

[buhaj47]

I have used the below code to allow people only paste values into a workbook:

Please Login or Register  to view this content.

The issue is cells data is pasted to are data validated. The VBA code ignores it and lets paste values that would not normally pass validation. How to fix this?

[mehmetcik]

I cannot see any Event Macro that will recognise a paste.

So

I would use a worksheet change macro to find all the cells with data validation and check that they are all compliant.

If not I would use the Undo function.

[PCI]

Please attach an Excel sample

[mehmetcik]

Please Login or Register  to view this content.

[Zer0Cool]

Copy/paste destroys the data validation. Its basically treated like a format.

You can replicate the same thing manually doing a copy paste over data validation (even just copy/paste, not special).

You need to check the values using the worksheet change event to enforce this if using copy/paste. Its likely you would face similar issues or errors using range.value to assign the value from another cell vs copy/paste.

You may consider essentially getting or storing the validation rules so you may re-apply them post paste, but I think youll still need to check for and enforce it via VBA to prevent pasting undesired values.

[mehmetcik]

I came up with a better solution.

I have started it for you.

Please Login or Register  to view this content.

[buhaj47]

I have attached an example - when you provide a value manually, then data validation will tell you if you got it wrong. With the VBA code, I can put something like 5400% and the error is not recognized.