Valdiation

[Jean-Paul De Winter]

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

 

[keepITcool]

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 :

 

[keepITcool]

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 :

 

[Jean-Paul De Winter]

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]

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 :

 

[Jean-Paul De Winter]

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]

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 :

 

[Jean-Paul De Winter]

I must apologize... I completely misunderstood!!!
It works
Thanks for your patience
JP