Cell with data validation allows blanks, pastes, deletions - How do I protect?

[fuzzyfreak]

Hi, I have a few cells that use Data Validation and point to name
lists for their entries. I use a Stop Error for invalid entries an
this works so long as you type directly into the cell.

My problem is that if the user decides to use either delete or paste
they can get around the error and input something into the cell that i
not within the list - this inlcudes blanks, which I do not want. Surel
this is a flaw in Data Validation?

So, how do I force them to only select an entry from the list?

Many thanks

Fuzz

 

[mzehr]

Hi,
You have noted a weakness of Data Validation, which can
partially be mitigated with VBA. However, even an
experinced user can override those additional safeguards.
You would need something like the following posted by JE
McGimpsey several months ago, and modify it to suit your
needs:


Put this in your worksheet code module (right-click the
sheet tab and
choose View Code):

Private Sub Worksheet_Change(ByVal Target As
Excel.Range)
If Intersect(Target, Range("C4")) Is Nothing Then
Exit Sub
With Range("C4")
If Not IsEmpty(.Value) Then
If Not ((.Value = "CY") Or (.Value
= "CFS")) Then
.ClearContents
.Activate
MsgBox _
"Cell C4 may only have the
values ""CY"" or ""CFS"""
End If
End If
End With
End Sub

JE McGimpsey