Data Validation - copy/paste

[Ken Valenti]

I use data validation often and know that copy/paste will not only skip
validation but also over-write the data validation. Copy/paste special will
skip validation but the rule remains in place.

Assuming I have code to re-instate the proper data validation, is there a
way to flag entries that don't comply with the data validation - or do I have
to write independent code to check entries that may have been pasted?

Excel 2003

THanks in advance!

 

[Otto Moehrbach]

Ken
You can use a Worksheet_Change event macro to look at the entry whenever
a change is made in the entry. That macro can then search the Data
Validation list for that entry and take whatever action you want if the
entry is not in that list. Something like the following perhaps. I assumed
your list is named MyList and the DV cell is A1. Note that this macro
resets the Data Validation in A1. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("MyList").Find(What:=Target.Value, LookAt:=xlWhole) Is
Nothing Then
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Target.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=MyList"
End With
MsgBox "You must select from the list.", 16, "Invalid Entry"
End If
End If
End Sub

 

[Ken Valenti]

Thanks for your reply - but that still requires to write code to match the
data validation.

Also, using a worksheet change event will disable "Undo" and since I
already have a Check Data macro that is run before submitting data to the
database, I just want to ensure that data validation has done it's job. That
way other people can change the data validation without having to change any
code.

Here's the closest thing I can come up with to do what I want.

ActiveSheet.CircleInvalid

I still don't know how to programatically check if invalid cells exist, or
what cells are invalid but, but can visibly see invalid entries.

Thanks again,

Ken

 

[Ken Valenti]

Here's the code I was looking for

Sub IdentifyInvalidEntries()
Cells.ClearComments
Dim TempCell As Range
For Each TempCell In ActiveSheet.UsedRange
If Not TempCell.Validation.Value Then TempCell.AddComment "Invalid Entry"
Next
ActiveSheet.CircleInvalid
End Sub