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.

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