Validation to force entry in a cell
 

Hi,

I need validation on a cell where if any value is entered in one cell the
user MUST enter a value in an adjacent cell. This must be easier than I'm
making it..

e.g

If they enter a number in say A2, a I need to ensure they can't progress
without also entering a number in A3.
Equally, if they enter a number in say A3, a I need to ensure they can't
progress without also entering a number in A2.


The numbers have logical relationship (i.e <=) , but are mutually exclusive,
I can't have one without the other.

Is this a custom formula in the Validation box?

Amendment! (why no Edit function!"

Should read

"The numbers have NO logical relationship (i.e <=) , "

"Matt D Francis" wrote:

Hi,

I need validation on a cell where if any value is entered in one cell the
user MUST enter a value in an adjacent cell. This must be easier than I'm
making it..

e.g

If they enter a number in say A2, a I need to ensure they can't progress
without also entering a number in A3.
Equally, if they enter a number in say A3, a I need to ensure they can't
progress without also entering a number in A2.


The numbers have logical relationship (i.e <=) , but are mutually exclusive,
I can't have one without the other.

Is this a custom formula in the Validation box?

The big question here is what do you mean by "progress"? You can setup code
to check if both cells are occupied when the user selects any cell other
than A2 & A3. Or if he tries to save the file. Or if he tries to close the
file. In short, what do you want the user to NOT be able to do unless those
two cells are occupied? HTH Otto
"Matt D Francis" wrote in message
...
Hi,

I need validation on a cell where if any value is entered in one cell the
user MUST enter a value in an adjacent cell. This must be easier than I'm
making it..

e.g

If they enter a number in say A2, a I need to ensure they can't progress
without also entering a number in A3.
Equally, if they enter a number in say A3, a I need to ensure they can't
progress without also entering a number in A2.


The numbers have logical relationship (i.e <=) , but are mutually
exclusive,
I can't have one without the other.

Is this a custom formula in the Validation box?

Hi Otto.

I think just not saving the worksheet would be sufficient. It maybe they
don't have that exact data to hand at that moment, but it definitely needs to
be there before the workbook is saved.

I thought it would be coding, but was hoping it wasn't!

Matt

"Otto Moehrbach" wrote:

The big question here is what do you mean by "progress"? You can setup code
to check if both cells are occupied when the user selects any cell other
than A2 & A3. Or if he tries to save the file. Or if he tries to close the
file. In short, what do you want the user to NOT be able to do unless those
two cells are occupied? HTH Otto
"Matt D Francis" wrote in message
...
Hi,

I need validation on a cell where if any value is entered in one cell the
user MUST enter a value in an adjacent cell. This must be easier than I'm
making it..

e.g

If they enter a number in say A2, a I need to ensure they can't progress
without also entering a number in A3.
Equally, if they enter a number in say A3, a I need to ensure they can't
progress without also entering a number in A2.


The numbers have logical relationship (i.e <=) , but are mutually
exclusive,
I can't have one without the other.

Is this a custom formula in the Validation box?

Matt
Below is the macro that you need. Note that this is a workbook macro
and, as such, it must be placed in the workbook module. To do this,
right-click on the Excel icon that is to the left of the word "File" in the
menu across the top of your worksheet, select "View Code", and paste this
macro into that module.
This macro is triggered when the user initiates the Save command.
As written, this macro will check cells A2 & A3 of the sheet "MySheet".
This macro checks for any content in both cells. If both cells are
occupied, the file will be saved. If either or both cells are empty, the
Save command is cancelled. In this case a message box will be displayed
advising the user that both cells have to be filled before the file can be
saved.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Application.CountA(Sheets("MySheet").Range("A2:A3" )) < 2 Then
Cancel = True
MsgBox "Both A2 & A3 must be filled before this workbook can be
saved.", 16, "Save Cancelled"
End If
End Sub
Expand this message to avoid line wrap.
If you wish, send me direct via email a valid email address for you and I
will send you a small file with this macro properly placed. My email
address is . Remove the "nop" from this address. HTH
Otto
"Matt D Francis" wrote in message
...
Hi Otto.

I think just not saving the worksheet would be sufficient. It maybe they
don't have that exact data to hand at that moment, but it definitely needs
to
be there before the workbook is saved.

I thought it would be coding, but was hoping it wasn't!

Matt

"Otto Moehrbach" wrote:

The big question here is what do you mean by "progress"? You can setup
code
to check if both cells are occupied when the user selects any cell other
than A2 & A3. Or if he tries to save the file. Or if he tries to close
the
file. In short, what do you want the user to NOT be able to do unless
those
two cells are occupied? HTH Otto
"Matt D Francis" wrote in
message
...
Hi,

I need validation on a cell where if any value is entered in one cell
the
user MUST enter a value in an adjacent cell. This must be easier than
I'm
making it..

e.g

If they enter a number in say A2, a I need to ensure they can't
progress
without also entering a number in A3.
Equally, if they enter a number in say A3, a I need to ensure they
can't
progress without also entering a number in A2.


The numbers have logical relationship (i.e <=) , but are mutually
exclusive,
I can't have one without the other.

Is this a custom formula in the Validation box?