Create a warning message

  • Thread starter Thread starter Stuart M
  • Start date Start date
S

Stuart M

Cell validation only works when data is entered directly in a cell. I want to
recreate this effect so that Excel displays a pop up message when the
calculated value of a cell falls below £100. The cell in question is
calculating the sum of three other cells on the worksheet and is formatted as
currency.

Cheers :)
 
Hi,

Because it is a calculated value you can use the worksheet calculate event

Private Sub Worksheet_Calculate()
If Range("A1").Value < 100 Then
MsgBox "Cell value " & Range("A1").Value
End If
End Sub

Mike
 
Somthing like the below. Right click sheet tab>View Code and paste the below
code..A1:C1 is the range you enter the values...and D1 is the cell with
formula. Adjust to suit..

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Application.Intersect(Target, Range("A1:C1")) Is Nothing Then
If Range("D1") < 100 Then
MsgBox "Total of A1:C1 should be more than 100"
Target.Value = 0: Target.Activate
End If
End If

Application.EnableEvents = True
End Sub
 
you might also consider using Conditional Formatting - ie let the cell turn
red or something to make it really visible
 
Hi Mike thanks for the answer. Its the value returned in the Sum cell that
must be used for validation - i.e. if it shows <£100 then show the warning
message.

The users input data somewhere else which is summed in a different cell so I
want the summed cell to display a warning if it is less than a given amount.

Any ideas?
 
Hi Jacob thanks for the answer. I couldn't get it to work at all. I just want
a warning displayed if the value is below a certain monetary figure. The
simple formula in that cell is =SUM(I46:J48)

so if that result is >100 show the warning message

Any ideas?
 
Sorry I meant less than < DOH

Stuart M said:
Hi Jacob thanks for the answer. I couldn't get it to work at all. I just want
a warning displayed if the value is below a certain monetary figure. The
simple formula in that cell is =SUM(I46:J48)

so if that result is >100 show the warning message

Any ideas?
Click to expand...
 
I'm being lazy I know but can you tell me how?

Thnaks. Probably the low-tec way might be best!
 
Select the sum cell. From menu Format>Conditional Formatting.

Condition1
CEll value is>lessthan>100
Select a color from 'Format'

If this post helps click Yes
 
Back
Top