help with restricting paste in cells with data validation

  • Thread starter Thread starter hawk
  • Start date Start date
H

hawk

I have a range (column infact) in which i would like users to only
select options from the drop-down... i added the following code that
prevent paste into cell with data validation from the same excel...

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



but this does NOT prevent paste from another instance of excel file...
if there a way to prevent paste from another session of excel
 
hawk said:
I have a range (column infact) in which i would like users to only
select options from the drop-down... i added the following code that
prevent paste into cell with data validation from the same excel...

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
Click to expand...

Insufficient. One clever work-around would be copying from a different
workbook from a range with the validation rule (formula) =TRUE. Once
pasted, it'll have a validation rule, but not the one you want.
if there a way to prevent paste from another session of excel
Click to expand...

Not really. You can use event handlers to flush the clipboard, but no
Excel event is triggered by switching to any other application then
switching back to Excel. You'd need a Change event handler which
references a table containing range addresses (qualified with
worksheet names) in the first column and possible valid entries in the
subsequent columns. Check if the cells changed overlap any of the
ranges in this table, and if so check if the current entry is valid,
and if not undo.

This means redundancy, but you could automate populating such a table.
There's really no alternative.
 
Back
Top