Validation Rules

  • Thread starter Thread starter Manos
  • Start date Start date
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
 
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)
 
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...
 
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)
 
Manos

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

Andy,
 
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).
 
Back
Top