Forcing the completion of a particular cell

[ss]

Is there an easy way to force the completion of a particular cell before
the others are completed.
eg
I want a date put in cell D3 after this has been done they complete
cells C8:C13 and D813 (dates)

If they dont complete cell D3 first it throws out the calculations in
the others. Its not a big issue but if easily resolved it would make it
tidier. Could conditional format be used ? if D3 is blank then the
other cells have white text to hide the input.

 

[Harlan Grove]

Right click sheet tab>view code> insert this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("c8:d13")) Is Nothing Then
If Len(Application.Trim(Range("d3"))) < 1 Then
cancel = True
MsgBox "Fill in D3 first"
End If
End If
End Sub

....

Data validation for C813 would be simpler. The custom validation
rule would be

=COUNT($D$3)

Since any number would satisfy this, maybe

=ABS($D$3-TODAY())<=1461

which would be true if the date in D3 were within 4 years either
before or after the current date.

Yes, data validation can be defeated by pasting plain text, but your
event handler could be defeated by entering a single space character
in D3.

 

[isabelle]

hi ss,


you can use Excel data validation

=NOT(ISBLANK($D$3))

or an event macro

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Target, Range("C813"))
If Not isect Is Nothing Then
If Range("D3") = "" Then MsgBox "fill cell (D3) before fill in the cells (C8:C13 and D813) "
End If
End Sub




--
isabelle




Le 2012-02-02 16:20, ss a écrit :

Is there an easy way to force the completion of a particular cell before the others are completed.
eg
I want a date put in cell D3 after this has been done they complete cells C8:C13 and D813 (dates)

If they dont complete cell D3 first it throws out the calculations in the others. Its not a big issue

but if easily resolved it would make it tidier. Could conditional format be used ?

if D3 is blank then the other cells have white text to hide the input.

 

[ss]

hi ss,


you can use Excel data validation

=NOT(ISBLANK($D$3))

or an event macro

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Target, Range("C813"))
If Not isect Is Nothing Then
If Range("D3") = "" Then MsgBox "fill cell (D3) before fill in the cells
(C8:C13 and D813) "
End If
End Sub

Not comfortable on using a macro, plus it causes issues when programme
sent to others.

Ok nearly there with using =NOT(ISBLANK($D$3))
However it is working the wrong way around, if I type in D3 it makes the
other cells go blank, if I leave D3 blank the dates show in the other
cells. I have tried checking the true and untrue box in conditional
format but makes no difference.