I'm trying to add data validation to a worksheet, but when I paste a value, the data validation is removed, as well as the conditional formatting.
I've tried "Protect Workbook" but I can't find any option that will allow editing of values in locked cells while preserving everything else.
I've also tried about five different methods of writing some VBA to do this but so far none of them work, mostly due to not being able to save the old properties before the new values are applied.
How can I lock the sheet or write some VBA to lock everything about a cell except its value? I'm using Excel 2016 but my users might not be. I need a solution that will work in as many older versions as possible.
I am aware that "Paste Special" will paste only values, but I am giving this spreadsheet to users to fill in and return, and I'm not confident that they'll understand or remember to "Paste Special" every time, resulting in us trying to use invalid data.