Multiple cell validation on one line

[ServiceChris]

Hi all. Back for my last query of the week (I hope)

I have a sheet with lots of nice validation on it - some in the for
of macros and some just data validation. However, the one last thing
would like to validate is that an entry has been put in all th
mandatory fields before the user leaves that line. So, for example
once they enter a value in C4, they must also enter one in C6, C7, C9
C10, and optionally in C5 & C9. The same works whichever field the
first enter data in. Once one field (optional or mandatory) is filled
I do not want them to be able to leave the line without all th
mandatory ones being filled. They can move left and right as much a
they want, but not up or down, or use the mouse to click elsewher
without a full row being entered.

Any suggestions?

Thanks in advance.

Chri

 

[NickHK]

Chris,
You would probably find that easier to achieve with a modal user form, then
pass the data to the correct cells.

NickHK

 

[Bob Phillips]

Hi Chris,

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
If Target.Column > 1 Then
If Me.Cells(4, Target.Column - 1).Value <> "" Then
Set rng = Union(Me.Cells(6, Target.Column - 1), Me.Cells(7,
Target.Column - 1), _
Me.Cells(9, Target.Column - 1), Me.Cells(10,
Target.Column - 1))
If WorksheetFunction.CountA(rng) <> 4 Then
MsgBox "Previous line incomplete"
Me.Cells(4, Target.Column - 1).Select
End If
End If
End If
End Sub

--

HTH

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