Data Validation

[gimli]

Hello all,

Trying to add multiple data validations for a cell and having hard time getting conditions to work.
Restrictions are numbers between 0 and 100 or just the text value of x. Any suggestions
would be great!

thanks!

 

[starl]

Assuming your validation is setup in A1, use a Custom validation setting with the following formula:

=OR(AND(A1>=0,A1<=100),A1="x")

 

[Paul Ked]

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim i As Long, str As String
    If Target.Address = "$A$1" Then
        str = Range(Target.Address)
        If IsNumeric(str) Then
            If Val(str) < 0 Or Val(str) > 100 Then GoTo Wrong
        End If
        If Not IsNumeric(str) Then
            If str <> "x" And str <> "X" Then GoTo Wrong
        End If
    End If
    Application.EnableEvents = True
    Exit Sub
Wrong:
    MsgBox "Only allowed 0-100 or x"
    Range(Target.Address) = ""
    Range(Target.Address).Select
    Application.EnableEvents = True
End Sub

 

[Paul Ked]

Sorry, didn't see your post Tracy!

 

[starl]

Sorry, didn't see your post Tracy!

No biggee. Alternative solutions is what makes this site great
PS: wait till you see the updated site! Been testing for 2 weeks and I love it!

 

[Paul Ked]

I agree. New site? You've got me all excited

 

[gimli]

Thanks for the help...though I responded Friday....maybe didnt hit pst reply.

Anyways,
this code worked perfect.
=OR(AND(A1>=0,A1<=100),A1="x")

Is there a way to add one more validation to that code? I just want to allow integers to be entered..no decimal points.
is that doable ?

Thanks much

 

[Peter_SSs]

Is there a way to add one more validation to that code? I just want to allow integers to be entered..no decimal points.
is that doable ?

Try
=OR(AND(A1>=0,A1<=100,IFERROR(INT(A1),A1)=A1),A1="x")

 

[gimli]

Ah works great thanks!!

 

[Peter_SSs]

Ah works great thanks!!

You're welcome. Thanks for the follow-up.