Does anyone know a way to allow data to be copied and pasted into a
spreadsheet that contains data validation in some areas and retain the
validation on those cells? Ideally I would like the ability to allow the
operation under all circumstances unless the action breaches one or more
validation rules.
Whatever the solution it must be able to handle:
1. One cell copied to one cell in the same sheet when target cell has
validation
2. One cell copied to one cell in the same sheet when target cell has no
validation
3. A block of cells copied to to a block of cells in the same sheet where no
cells have validation
4. A block of cells copied to to a block of cells in the same sheet where
ALL cells have validation
5. A block of cells copied to to a block of cells in the same sheet where
some but not all cells have validation
And all of the above if the copied cell / data comes from outside the sheet
/ workbook.
So far I have tried some VBA code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub
Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
This works unless you copy a block of data from outside the sheet. It's also
not too flexible but I feel it's on the right lines.
What would be good is to be able to lock down the range of cells containing
validation to prevent them being able to be pasted on even in a block but
still allow manual entry.
|
Similar Threads
