CODEWITHSUNDEEP
validationvbacopypaste
user2667934
user2667934

Reputation: 11

Prevent copying and pasting a cell from a column with data validation to another column also with data validation

I have a worksheet where I have already prevented a user from copying and pasting a cell in a normal column into another cell in a column with data validation drop-downs.

The issue I'm now having is that a user is able to copy and paste a cell from a column with data validation into another cell in a column with data validation. Is there any way to prevent this??

Many thanks, Kieran

Upvotes: 1

Views: 3949

Answers (1)

SeanC
SeanC

Reputation: 15923

You can check that the cells still have validation after a change.

Using the Workbook change event, in the ThisWorkbook module

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Does the validation range still have validation?
If Not HasValidation(Range("RangeToProtect1")) Then RestoreValidation
If Not HasValidation(Range("RangeToProtect2")) Then RestoreValidation
If Not HasValidation(Range("RangeToProtect3")) Then RestoreValidation
If Not HasValidation(Range("RangeToProtect4")) Then RestoreValidation
End Sub

Private Sub RestoreValidation()
Application.EnableEvents = False
'turn off events so this routine is not continuously fired
Application.Undo
Application.EnableEvents = True
'and turn them on again so we can catch the change next time
MsgBox "Your last operation was canceled." & _
       "It would have deleted data validation rules.", vbCritical
End Sub

Private Function HasValidation(r) As Boolean
'   Returns True if every cell in Range r uses Data Validation
On Error Resume Next
Debug.Print r.Validation.Type    'don't care about result, just possible error
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

in RangeToProtect, you can either specify a specific range, or use a named range (note that a named range, while making code easy to read, can cause the code to fail if they delete the entire range that you are trying to protect)

Upvotes: 1

Related Questions