Thanks for the reply spitfireblue - I had seen and tried that code from another site but never could get it to work; I think while the code might have worked fine by itself, it didn't want to play nice with the other code I needed in the workbook.
I've managed to get this working successfully using the code in this workbook (also pasted below); There is code in each sheet, plus code in the workbook, as well as a macro saved with a shortcut key of Ctrl + v to override the Ctrl+v function with 'paste values only'.
This workbook does the following:
1) Ensure the end user enables macros, because they cannot see any of the sheets except the warning sheet telling them to enable macros if they don't
2) Replaces both the context menu and Ctrl+v paste commands with "paste special values only" to ensure that the Data Validation on certain cells is not overwritten.
3) once the data is pasted, it looks at every cell with validation and compares the data entered with data validation choices; any that don't match initiate a pop-up asking the user to correct the data.
enable_macros-paste_values_only-post_paste_validation.xlsm
Credits to mikeerickson from post: http://www.excelforum.com/excel-prog...ion-cells.html
AND
Ken C Johnson (from another site) for sharing their code that I was able to reuse here.
Bookmarks