Data Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to use data validation (or another Excel method) to prevent an input into a cell that is going to cause formulas in other cells that are dependent on the input cell to have a negative value? Thank you again for your assistance.
 
Are the dependent cells on the same worksheet?

If yes, you could use a worksheet_change event, and look for negative values in
the dependent cells. If you find one, revert back before the change and issue a
message.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myDependents As Range
Dim myCell As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Set myDependents = Target.Dependents
For Each myCell In myDependents.Cells
With myCell
If IsNumeric(.Value) Then
If .Value < 0 Then
With Application
.EnableEvents = False
.Undo
End With
MsgBox "You turned: " & .Address(0, 0) & _
" Negative!" & vbLf & "Reverting!"
Exit For
End If
End If
End With
Next myCell

errHandler:
Application.EnableEvents = True

End Sub

I used A1 as my cell that could cause the problem.

And a couple of links that may help:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
and
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top