Validation Rules

M

Manos

Hello everybody,

does anyone know if i can run two or more validations in
the same cell or in the same sheet?
For example i want to create in one cell a restriction
that will no allow any number except
2,3,4,5,6,7,8,9,10,11,12 and at the same time for the
numbers 3,6,9,12 i want to provide an information
like "please complete forms 20,21,22,etc."

I tried to put one validation in one cell for the
restriction cell B1 and i set the second validation in the
cell B2. i also set B2=B1 in order ot have the same number
but the validation for B2 doesn't work properly
Any advices?

thanks in advance
Manos
 
B

Bob Phillips

Manos,

You could add worksheet change event code.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Address = "$B$1" Then
If Target.Value > 1 And Target.Value < 13 Then
If Target.Value Mod 3 = 0 Then
MsgBox "Please complete forms 20, 21, 22"
End If
Else
MsgBox "Invalid value"
Target.Value = ""
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

This goes in the worksheet code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Manos

there is no other way except VB?
Thanks a lot Bob
-----Original Message-----
Manos,

You could add worksheet change event code.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Address = "$B$1" Then
If Target.Value > 1 And Target.Value < 13 Then
If Target.Value Mod 3 = 0 Then
MsgBox "Please complete forms 20, 21, 22"
End If
Else
MsgBox "Invalid value"
Target.Value = ""
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

This goes in the worksheet code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
 
B

Bob Phillips

There may be, but it's not obvious to me.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

Andy B

Manos

How about a workaround? Try conditional formatting an adjacent cell as
required. Would that do?

Andy,
 
E

Earl Kiosterud

Manos,

You can put a formula somewhere that provides the message:
=IF(OR(A2={3,6,9,12}),"please complete forms...", "")

Or a single formula that looks at the entire column:
=IF(OR(A2:A16={3,6,9,12}),"please...","")
This one must be array-entered (Ctrl-Shift-Enter).
 
Top