Data validation - copy/paste problem

Hello all

I want to prevent copying and pasting over cells with data validation as this means the validation is overwritten. To get around this I've selected all the cells with data validation and unlocked them, then protected the worksheet with all boxes ticked apart from format cells, columns and rows.

This prevents copy/pasting from overwriting data validation but it doesn't prevent data being pasted in that doesn't meet the validation criteria.

So for example, say -1 is in cell A1, with no data validation. In B1 there is data validation, which doesn't allow negative numbers to be entered. If I copy and paste A1 into B1, the data validation isn't overwritten, but it doesn't stop the non validated data (-1) from being entered! If I then double click on B1 and press enter it recognises the validation criteria is not met.

This workbook will be shared, if that makes any difference.

Thanks

bump...anyone?

What do you want it to do if it's outside range? Stop the copypaste operation? My gut feeling is that's going to be hard; you're interfering with the clipboard, not with excel. You might be able to do it with VBA but I don't think it would be very robust.

My advice is, either validate with a helper column that will throw an error if it's data outside range, or add conditional formatting to alarm cells that are outside range (or both).

That way people can copy-paste the data in without any problems, but the spreadsheet can still validate.

Hi Ben

Thanks for the reply. I want to stop the copypaste operation, yes.

The thing is, I've got a lot of columns, each with different validation. So I suppose I'd need one of these helper columns for each column that I have validation in?

I can see that getting quite messy..