| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation Rule anomaly
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation Rule anomaly
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 "Bob" wrote: 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 "Toppers" wrote: try: =AND(NOT(ISNUMBER(FIND(" ",A2))),NOT(ISNUMBER(FIND(CHAR(160),A2)))) HTH "Bob" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation Rule anomaly
Thanks for all your help! i sincerely appreciate.
Bob "Toppers" wrote: Change "If Target.address ..." to "If Target.Column = 1 Then" "Bob" wrote: 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 "Toppers" wrote: 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 "Bob" wrote: 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 "Toppers" wrote: try: =AND(NOT(ISNUMBER(FIND(" ",A2))),NOT(ISNUMBER(FIND(CHAR(160),A2)))) HTH "Bob" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation Rule anomaly
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 "Toppers" wrote: try: =AND(NOT(ISNUMBER(FIND(" ",A2))),NOT(ISNUMBER(FIND(CHAR(160),A2)))) HTH "Bob" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation Rule anomaly
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 "Toppers" wrote: 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 "Bob" wrote: 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 "Toppers" wrote: try: =AND(NOT(ISNUMBER(FIND(" ",A2))),NOT(ISNUMBER(FIND(CHAR(160),A2)))) HTH "Bob" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation Rule anomaly
try:
=AND(NOT(ISNUMBER(FIND(" ",A2))),NOT(ISNUMBER(FIND(CHAR(160),A2)))) HTH "Bob" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Validation Rule anomaly
Change "If Target.address ..." to "If Target.Column = 1 Then"
"Bob" wrote: 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 "Toppers" wrote: 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 "Bob" wrote: 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 "Toppers" wrote: try: =AND(NOT(ISNUMBER(FIND(" ",A2))),NOT(ISNUMBER(FIND(CHAR(160),A2)))) HTH "Bob" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Identifying formulas using a Validation rule | Excel Worksheet Functions | |||
| validation rule - force text entries to appear as Proper | Excel Worksheet Functions | |||
| Validation rule - custom 5 digits w/ leading zeroes | Excel Worksheet Functions | |||
| Validation rule | Excel Discussion (Misc queries) | |||
| validation rule | Excel Worksheet Functions | |||
Linear Mode
