i thought this was resolved. OK, here's another way
create a table which is a three column of cells that represents each cell
that has to be in sequence and
put it on any sheet
the code adds the validation to all the cells.
eg
A B C
1 sheet cell dependsOn
2 Sheet1 C5 C1
3 Sheet1 D1 C5
4 Sheet1 D5 D1
5 Sheet1 E1 D5
6 Sheet1 E5 E1
so A2 says sheet1, and that Cc depends on C1 having data
Sub SetValidation()
Dim cell As Range
Dim target As Range
For Each cell In Range("A2:A10") ' as required
Set target = Worksheets(cell.Value).Range(cell.Offset(, 1))
With target.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=" & cell.Offset(, 2) & "<>"""""
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = "Warning"
.ErrorTitle = cell.Offset(, 2) & " must be filled"
.InputMessage = "Please ensure linked cell is filled in" & Chr(10) &
""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Next
End Sub
lets put this in Range A1 : A100 on sheet2 whith sheet1 as our main sheet,
where your user enters data
so if the user enters a value into C1 the code will discover that the
preceding cell is D5, and if that's empty, raise an error
