Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Matt D Francis
 
Posts: n/a
Default 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?
  #2   Report Post  
Matt D Francis
 
Posts: n/a
Default

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?

  #3   Report Post  
Otto Moehrbach
 
Posts: n/a
Default

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?



  #4   Report Post  
Matt D Francis
 
Posts: n/a
Default

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?




  #5   Report Post  
Otto Moehrbach
 
Posts: n/a
Default

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?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell Validation DejaVu Excel Discussion (Misc queries) 7 June 20th 05 10:33 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
up to 7 functions? ALex Excel Worksheet Functions 10 April 12th 05 06:42 PM
Cell Validation Simon Shaw Excel Discussion (Misc queries) 5 March 4th 05 12:51 AM
how to add two data validation lists to a single cell? Mike Peter Excel Worksheet Functions 1 December 8th 04 09:22 PM


All times are GMT +1. The time now is 08:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"