Highlighted copy range during Excel copy-paste

[ssakthivel]

Hello

I wonder if it is possible to access the copy-from range after the user has selected the destination range during Excel Copy-Paste.

Here is my situation. I have setup Excel Data Validations such as List, Decimal between and Text Length on certain cells. These validations work fine when user enters data in these cells ensuring only valid data go into these cells. However when user copies and pastes into these cells, values that will fail the validations still get pasted into these cells.

I do not want to disable the Excel Copy-paste function altogether. What I would like to do is override the Excel's paste function (Control V or Shift Insert) with my function that will read the copy-from range, programmatically validate the data and then paste the data values into the destination cell(s).

This thread (http://www.mrexcel.com/forum/showthread.php?t=56674) has most of what I am looking for. I need to know how to read the selected copy-from range after user has selected destination range.

Thanks

 

[Weaver]

Would it work to make a backup of the cells being pasted to, using Worksheet_SelectionChange event then programattically vet the data after posting using Worksheet_Change and restore the original data if it doesn't pass muster?

 

[ssakthivel]

Here is how I achieved what I wanted:

Dim DataObj As New MSForms.DataObject
Dim s As String
DataObj.GetFromClipboard
On Error Resume Next
s = DataObj.GetText

The string variable s had the data from highlighted range.

Thanks Weaver for the workaround suggestions.