Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data validation vs. cutting pasting
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protect sheets against cutting / pasting | Excel Worksheet Functions | |||
Formats when cutting and pasting | Excel Discussion (Misc queries) | |||
relative references when cutting/pasting | Excel Discussion (Misc queries) | |||
Problem encountered when cutting and pasting | Excel Discussion (Misc queries) | |||
when cutting and pasting to different files the year changes... w. | Excel Worksheet Functions |