Hi,
I have a data validation list in a particular range of a worksheet which I am using at work. But, as it happens if someone copies and pastes values from other cells (within the same worksheet or other worksheets or other workbooks) the values in the data validation cells gets overwritten.
Now I have done quite a bit of research online, and it seems like MS has not addressed this issue till date. The best solution which I was able to find online was a VBA script which could help overwriting of data validation cells. The code is as follows:
Sub Worksheet_Change(ByVal Target As Range)
'where DataRange is the range of cells with data validation list values
If HasValidation(Range("DataRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub
Function HasValidation(r) As Boolean
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
Though this code is helping with the above requirement, it is not a complete solution. Above code helps protect the copy-paste within the worksheet, but if you copy a cell from another worksheet or another workbook, the data validation cell gets overwritten.
Any expert out there who can solve this? I have spent hours online to find a solution but to no avail.
Help would be much appreciated!!
Regards,
Amit
Bookmarks