Validation to force entry in a cell

[Matt D Francis]

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 D Francis]

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?

[Otto Moehrbach]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 D Francis]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

[Otto Moehrbach]

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 [email protected]. Remove the "nop" from this address. HTH
Otto
"Matt D Francis" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > 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?
>>
>>
>>