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
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