| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Valdiation
Hi,
I set up a validation rule when entering cells Only numbers lower the 10 are allowed. When I enter 11 I get a warning asking if, eventually I want to accept the entry. NO WAY to accept so... how to avoid the quetion from being asked? All I want is just to refuse the entry. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Valdiation
DV will give warnings or it wont work as you intend. Alternative: replace the DV with an event handler for the sheet. e.g. all cells with Yellow background. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub ElseIf Len(Target) = 0 Then Exit Sub End If 'Process this for all cells with a YELLOW fill. If Target.Interior.ColorIndex = 6 Then Select Case Target.Value Case 0 To 10 'allow integers only If Target.Value2 < CLng(Target.Value2) Then Target.Value2 = CLng(Target.Value2) End If Case Else Beep Target.ClearContents End Select End If End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Hi, I set up a validation rule when entering cells Only numbers lower the 10 are allowed. When I enter 11 I get a warning asking if, eventually I want to accept the entry. NO WAY to accept so... how to avoid the quetion from being asked? All I want is just to refuse the entry. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Valdiation
Another one would be: to work with the existing DV.. Turn OFF the Error Message.. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Count 1 Then Exit Sub ElseIf Len(Target) = 0 Then Exit Sub ElseIf IsError(Target.Validation.Type) Then Exit Sub End If On Error GoTo 0 With Target.Validation If Not .ShowError And Not .Value Then Target.ClearContents Beep End If End With End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Hi, I set up a validation rule when entering cells Only numbers lower the 10 are allowed. When I enter 11 I get a warning asking if, eventually I want to accept the entry. NO WAY to accept so... how to avoid the quetion from being asked? All I want is just to refuse the entry. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Valdiation
Now THAT'S a good solution, but...
I don't hear a "beep" and... is it possible to show a message saying something like "ENTRY INCORRECT"? Thanks "keepITcool" schreef in bericht ... Another one would be: to work with the existing DV.. Turn OFF the Error Message.. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Count 1 Then Exit Sub ElseIf Len(Target) = 0 Then Exit Sub ElseIf IsError(Target.Validation.Type) Then Exit Sub End If On Error GoTo 0 With Target.Validation If Not .ShowError And Not .Value Then Target.ClearContents Beep End If End With End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Hi, I set up a validation rule when entering cells Only numbers lower the 10 are allowed. When I enter 11 I get a warning asking if, eventually I want to accept the entry. NO WAY to accept so... how to avoid the quetion from being asked? All I want is just to refuse the entry. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Valdiation
now I';m confused.. I thought the whole point was NOT displaying a message! else you'd better just change the error message in the standard Data validation. a STOP means the user will never be allowed to enter invalid data a WARNING/INFORMATION means he'll get a warning but can still enter invalid data. you can THEN use my code (slightly adapted) to check the validation's VALUE (it's a boolean indicating Valid/INVALID) if you cant hear the beep.. check volume control. VBA's beep uses the soundcard not the systemspeaker. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Now THAT'S a good solution, but... I don't hear a "beep" and... is it possible to show a message saying something like "ENTRY INCORRECT"? Thanks "keepITcool" schreef in bericht ... Another one would be: to work with the existing DV.. Turn OFF the Error Message.. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Count 1 Then Exit Sub ElseIf Len(Target) = 0 Then Exit Sub ElseIf IsError(Target.Validation.Type) Then Exit Sub End If On Error GoTo 0 With Target.Validation If Not .ShowError And Not .Value Then Target.ClearContents Beep End If End With End Sub -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Hi, I set up a validation rule when entering cells Only numbers lower the 10 are allowed. When I enter 11 I get a warning asking if, eventually I want to accept the entry. NO WAY to accept so... how to avoid the quetion from being asked? All I want is just to refuse the entry. Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Valdiation
OK... but
With the validation thing there is always a way to accept what you entered. Your way is perfect but a small message sayin something like "What you enterd is not allowed" would be great... (BTW I don't think tere are speakers connected to the computers the file is running on so they wil never hear a beep) JP "keepITcool" schreef in bericht ... now I';m confused.. I thought the whole point was NOT displaying a message! else you'd better just change the error message in the standard Data validation. a STOP means the user will never be allowed to enter invalid data a WARNING/INFORMATION means he'll get a warning but can still enter invalid data. you can THEN use my code (slightly adapted) to check the validation's VALUE (it's a boolean indicating Valid/INVALID) if you cant hear the beep.. check volume control. VBA's beep uses the soundcard not the systemspeaker. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Now THAT'S a good solution, but... I don't hear a "beep" and... is it possible to show a message saying something like "ENTRY INCORRECT"? Thanks "keepITcool" schreef in bericht ... Another one would be: to work with the existing DV.. Turn OFF the Error Message.. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Count 1 Then Exit Sub ElseIf Len(Target) = 0 Then Exit Sub ElseIf IsError(Target.Validation.Type) Then Exit Sub End If On Error GoTo 0 With Target.Validation If Not .ShowError And Not .Value Then Target.ClearContents Beep End If End With End Sub -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Hi, I set up a validation rule when entering cells Only numbers lower the 10 are allowed. When I enter 11 I get a warning asking if, eventually I want to accept the entry. NO WAY to accept so... how to avoid the quetion from being asked? All I want is just to refuse the entry. Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Valdiation
PLEASE READ my previous post very carefully... and then i'll repeat myself: if the DV's Error Alert style is STOP, users CANNOT proceed when they enter invalid data. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : OK... but With the validation thing there is always a way to accept what you entered. Your way is perfect but a small message sayin something like "What you enterd is not allowed" would be great... (BTW I don't think tere are speakers connected to the computers the file is running on so they wil never hear a beep) JP "keepITcool" schreef in bericht ... now I';m confused.. I thought the whole point was NOT displaying a message! else you'd better just change the error message in the standard Data validation. a STOP means the user will never be allowed to enter invalid data a WARNING/INFORMATION means he'll get a warning but can still enter invalid data. you can THEN use my code (slightly adapted) to check the validation's VALUE (it's a boolean indicating Valid/INVALID) if you cant hear the beep.. check volume control. VBA's beep uses the soundcard not the systemspeaker. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Now THAT'S a good solution, but... I don't hear a "beep" and... is it possible to show a message saying something like "ENTRY INCORRECT"? Thanks "keepITcool" schreef in bericht ... Another one would be: to work with the existing DV.. Turn OFF the Error Message.. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Count 1 Then Exit Sub ElseIf Len(Target) = 0 Then Exit Sub ElseIf IsError(Target.Validation.Type) Then Exit Sub End If On Error GoTo 0 With Target.Validation If Not .ShowError And Not .Value Then Target.ClearContents Beep End If End With End Sub -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Hi, I set up a validation rule when entering cells Only numbers lower the 10 are allowed. When I enter 11 I get a warning asking if, eventually I want to accept the entry. NO WAY to accept so... how to avoid the quetion from being asked? All I want is just to refuse the entry. Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Valdiation
I must apologize... I completely misunderstood!!!
It works Thanks for your patience JP "keepITcool" schreef in bericht ... PLEASE READ my previous post very carefully... and then i'll repeat myself: if the DV's Error Alert style is STOP, users CANNOT proceed when they enter invalid data. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : OK... but With the validation thing there is always a way to accept what you entered. Your way is perfect but a small message sayin something like "What you enterd is not allowed" would be great... (BTW I don't think tere are speakers connected to the computers the file is running on so they wil never hear a beep) JP "keepITcool" schreef in bericht ... now I';m confused.. I thought the whole point was NOT displaying a message! else you'd better just change the error message in the standard Data validation. a STOP means the user will never be allowed to enter invalid data a WARNING/INFORMATION means he'll get a warning but can still enter invalid data. you can THEN use my code (slightly adapted) to check the validation's VALUE (it's a boolean indicating Valid/INVALID) if you cant hear the beep.. check volume control. VBA's beep uses the soundcard not the systemspeaker. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Now THAT'S a good solution, but... I don't hear a "beep" and... is it possible to show a message saying something like "ENTRY INCORRECT"? Thanks "keepITcool" schreef in bericht ... Another one would be: to work with the existing DV.. Turn OFF the Error Message.. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Count 1 Then Exit Sub ElseIf Len(Target) = 0 Then Exit Sub ElseIf IsError(Target.Validation.Type) Then Exit Sub End If On Error GoTo 0 With Target.Validation If Not .ShowError And Not .Value Then Target.ClearContents Beep End If End With End Sub -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Jean-Paul De Winter wrote : Hi, I set up a validation rule when entering cells Only numbers lower the 10 are allowed. When I enter 11 I get a warning asking if, eventually I want to accept the entry. NO WAY to accept so... how to avoid the quetion from being asked? All I want is just to refuse the entry. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
