preserve data validation when pasting values

[jackha18]

I have a sheet with data validation in some cells. When users paste values into these cell validation is replaced or erased by the validation of pasted cells.

Is there any way to protect cell validation when pasting data (without forcing users to always use Paste Values command).

 

[byundt]

John Walkenbach offers a Worksheet_Change sub to protect against deleting data validation during cut and paste operations at

http://j-walk.com/ss/excel/tips/tip98.htm

Walkenbach's code does not work with row insertion or deletion. If this is an issue, use a Worksheet_Calculate sub triggered by a volatile cell on the worksheet. A volatile cell is one containing a formula with a volatile function like NOW, TODAY, OFFSET, RAND, INDIRECT, CELL or INFO.

 

[jackha18]

thanks byundt!

I wonder is there any way to alter Excel Clipboard so that it will only contain values. As far as I know Excel COM does not provide interface to its Clipboard. Anybody here familiar with API functions to access and change Excel Clipboard?

I was also thinking about tracking Paste command. Then Undo it and generate Paste Values, but I still can't figure how a command can be tracked...

Well, I start with protection proposed by John Walkenbach.