John Walkenbach - Ensuring that Data Validation is Not Deleted

[al007]

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

The macro do not work if copied cells already contain validation -
specially if my range is a column & user will copy cells from the
column.

Can anybody help

 

[Tom Ogilvy]

Perhaps:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target,Range("ValidationRange")) is nothing then exit sub
If Application.CutcopyMode = True then
Application.CutCopyMode = False
MsgBox "Your can't paste to this Range." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub

 

[DM Unseen]

The following thread dealt with this

http://tinyurl.com/8b5lc

it extends on Jwalks code.

DM unseen

 

[Tom Ogilvy]

Just for Info,
didn't stop me from pasting a cell containing validation over an existing
cell containing validation - which was the issue here. I couldn't see
anything in the code that would check the old data validation type against
the new data validation type (just pasted) in the above situation despite
your description that it does.

 

[Tushar Mehta]

A way to validate data even if someone uses copy+paste is to simulate
the effect. This needs no VBA. The simulation doesn't stop bad data
from being entered. However, it makes the Excel workbook model
unusable and so protects data integrity.

An added benefit of this approach is that one can use validation
formulae that are disallowed in the data validation dialog box.

The idea is to put the validation in a cell that is protected. If the
entered data are unacceptable, the validation cell displays an error
and the value used in calculations becomes an error code. To make sure
of that, the rest of the model does *not* reference the cells in which
the user enters data but the cells that contain the correct value (or
an error code).

It's actually a lot easier than it appears. Go through the example
below and you'll see the simplicity of this approach.

Suppose in F4 we want the year between 2006 and 2010 and in F5 we want
the month as Jan, Feb, etc., preferably selected from a drop-down list.

Create the list of valid months in some range, say, N1:N12. Name this
range Months.

(Optionally) set data validation for F4 to be a list from the numbers
2006, 2007,..., 2010.

Set data validation for F5 to use a drop down list and enter =Months as
the list.

The above will work fine as long as someone doesn't paste new contents
into F4:F5. Here's how we validate data even after a paste operation!

The real validation will be in cells G4:H5.

In G4 enter =IF(OR(F4<2006,F4>2010,INT(F4)<>F4),"Please enter a
year"&CHAR(10)&"between 2006 and 2010",""). In H4 enter =IF(G4
="",F4,NA())

In G5 enter =IF(F5="","",IF(ISERROR(MATCH(F5,Months,0)),"Bad
month;"&CHAR(10)&"select from the drop down list"&CHAR(10)&"Please UNDO
any paste operations","")). In H5 enter =IF(F5="","",IF(G5="",F5,NA
())). Note that this is different from H4 because of what I believe to
be a bug in how data validation deals with named lists.

The rest of the worksheet/book uses H4 and H5 as the data cells. The
only references to F4:F5 are in the G4:H5 range.

Unlock F4:F5 and protect the worksheet. Now, irrespective of what the
user does to F4:F5, H4:H5 will contain either legitimate values or a
#N/A error code.

A slightly more advanced use of the above approach:

Suppose F6 is supposed to contain the day-of-the-month and we want to
correctly account for leap years between 2006 and 2010. Then, the G6
validation formula would be the array formula =IF(F6="","",IF(OR(F6
<1,F6>IF(OR(H5={"Jan","Mar","May","Jul","Aug","Oct","Dec"}),31,IF(OR(H5
={"Apr","Jun","Sep","Nov"}),30,IF(MOD(H4,4)=0,29,28)))),"Bad
date","")). And, of course, H6 contains =IF(G6="",F6,NA()).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[DM Unseen]

Tom,

that is true, it only checks Data Validation sheet intergrity, i.e.
when you have say a whole column(range) with one certain type of
validation, and you paste in a cell into that column with a different
type of validation from somewhere else, the code should block this
because it checks for the continuous range with the same validation.
For just one cell this is not a solution so this code will not help
there.

A trick that *could* be used is use my code and create a range of say 2
cells with the same validation, the second being a dummy cell that is
actually not needed for anything but the checking for a continous
validation range. Then my code would work as long as the enduser only
pastes his value in 1 of the 2 cells. Since this would mean both cells
have different validations, the code will spot this and cancel the
action.

Hope this clarifies it a bit

DM Unseen