stop entry of data that causes a negative value from a formula
 

Excel 2002. I tried to use "Validation" on the "Data" menu but that only
works on entered data. I have a formula in a cell and if the answer becomes
a negative number I want it to stop the user and "force" them to fix it. I
used an IF condition but that only displays a message.

stop entry of data that causes a negative value from a formula
 

Maybe you could fix the formula yourself:

=max(0,yourformula)
if the cell had to be non-negative.

Or you could put the message in that same cell...

=if(yourformula<0,"Please fix cells x, y, z",yourformula)

That might be enough to make it so that they can't advance further.

Other than that, you could add a worksheet event that popped up a message each
time the worksheet recalculated and saw that the cell was negative:

Option Explicit
Private Sub Worksheet_Calculate()

Dim myCell As Range

Set myCell = Me.Range("a1")

If myCell.Value < 0 Then
MsgBox "Hey, " & myCell.Address(0, 0) & " is negative"
End If

End Sub

That won't get them to change it, but it could get irritating after a while.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste it into that code window.

The back to excel and force a few recalc's (or change the cells to make A1
evaluate as negative).

Ps. change the address to what you need.

pmms wrote:

Excel 2002. I tried to use "Validation" on the "Data" menu but that only
works on entered data. I have a formula in a cell and if the answer becomes
a negative number I want it to stop the user and "force" them to fix it. I
used an IF condition but that only displays a message.

--

Dave Peterson

stop entry of data that causes a negative value from a formula
 

HI Pmms:
I had the exact same problem and it turned out to be an easy fix.
Example: In cell validation choose CUSTOMALLOWFORMULA (then restrict the
answer cell with a formula that restricts any amount less than zero) =a1=0.
MOST IMPORTANTLY - *In the Data Validation window select the error alert tab
and choose the style STOP. You should be good to go :-)

"pmms" wrote:

Excel 2002. I tried to use "Validation" on the "Data" menu but that only
works on entered data. I have a formula in a cell and if the answer becomes
a negative number I want it to stop the user and "force" them to fix it. I
used an IF condition but that only displays a message.

stop entry of data that causes a negative value from a formula
 

Pmms

What are you trying to prevent?

A1 from becoming negative.........enter =MAX(A3*A4,0) in A1

Or preventing a number in A3 or A4 from turning A1 negative?


Gord Dibben MS Excel MVP



On Fri, 23 Nov 2007 08:12:00 -0800, Teddy-B
wrote:

HI Pmms:
I had the exact same problem and it turned out to be an easy fix.
Example: In cell validation choose CUSTOMALLOWFORMULA (then restrict the
answer cell with a formula that restricts any amount less than zero) =a1=0.
MOST IMPORTANTLY - *In the Data Validation window select the error alert tab
and choose the style STOP. You should be good to go :-)

"pmms" wrote:

Excel 2002. I tried to use "Validation" on the "Data" menu but that only
works on entered data. I have a formula in a cell and if the answer becomes
a negative number I want it to stop the user and "force" them to fix it. I
used an IF condition but that only displays a message.