Validation Rule anomaly

[Guest]

I have the following custom validation rule that checks for, and disallows,
the inputting of a space or CHAR(160):

=AND(ISERROR(FIND(" ",A2))=TRUE,ISERROR(FIND(CHAR(160),A2))=TRUE)

However, if someone pastes some text containing a space, the validation rule
doesn't trap it!

Can anyone tell me why this occurs, and a possible fix to my validation rule?
Thanks,
Bob

 

[Guest]

As far as I am aware, any Data Validation does not execute if values are
pasted in.

Your best (only?) solution is to use a worksheet event module (VBA) to check
your condition.

Right click on w/sheet tab and copy/paste code below:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = True
If Target.Address = "$A$2" Then
c = Target.Value
If Application.Or(InStr(1, c, " ") > 0, InStr(1, c, Asc(160)) > 0) Then
Target.Value = ""
MsgBox "Invalid entry"
End If
End If
ws_exit:
Application.EnableEvents = True


End Sub

 

[Guest]

Thanks for all your help! i sincerely appreciate.
Bob

 

[Guest]

Thanks for the suggestion. Unfortunately, your solution still allows someone
to paste (instead of manually inputting) some text with a space, and not get
trapped by the validation rule.
Bob

 

[Guest]

Thanks for your help! As written, does your code work on all cells in column
A, or just A2? If the latter, can you tell me how to modify your code so it
works on all cells in column A? (I'm a novice when it comes to VBA.)
Thanks again,
Bob

 

[Guest]

try:

=AND(NOT(ISNUMBER(FIND(" ",A2))),NOT(ISNUMBER(FIND(CHAR(160),A2))))

HTH

 

[Guest]

Change "If Target.address ..." to "If Target.Column = 1 Then"