PDA

View Full Version : Copy & Pasting Overwrites My Data Validation



spacebase150
02-27-2014, 10:48 AM
Excel 2007

In the attached worksheet I have various data validation rules so those using the sheet can pick from the dropdown. However, sometimes users of the sheet copy and paste something into the cells with dropdown menus, thus removing my data validation.

In the "Pre" tab my data validation dropdowns are under cells C4:C18, C23:C35, then the same numbers in columns D, G, H, and I. In the "Post" tab I have similar drop-downs.

I need some VBA code to run that does not allow any of my data validation to be removed by copy or pasting through a range of non-contiguous cells that I can define.

I looked up this code and it works for only 1 range like C4:C18; but when I put in many ranges (non-contiguous) like so "C4:C18,C23:C35" it gets all messed up and freezes my excel:


Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("THIS IS WHERE I ENTER MY RANGE, NON-CONTIGUOUS MESSED IT UP")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
" It would have deleted data validation rules. Do not paste into the dropdown cells.", 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

Thank you for your help! If you think the above code is crap, scrap it and please just help me accomplish my goal of stopping people from over-writing my data validation while pasting.

Many, Many, Many thanks

Bob Phillips
02-28-2014, 10:12 AM
Try this approach


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("C4:C18,C23:C35")) Is Nothing Then

If HasValidation(Target) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was cancelled." & _
" It would have deleted data validation rules. Do not paste into the dropdown cells.", vbCritical
End If
End If

ws_exit:
Application.EnableEvents = True
Application.CutCopyMode = False
End Sub

Private Function HasValidation(ByRef r As Range) As Boolean
Dim x
' 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

spacebase150
02-28-2014, 11:21 AM
Nihil simul inventum est et perfectum! ;)

It didn't change anything at all. Did it work in my sample workbook?

Bob Phillips
02-28-2014, 12:41 PM
Yes it did, but I notice that it has lost an A in the last Application statement.

SamT
02-28-2014, 02:56 PM
fixed that for ya.