Create a warning message

[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

 

[Mike H]

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

 

[Jacob Skaria]

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

 

[Patrick Molloy]

you might also consider using Conditional Formatting - ie let the cell turn
red or something to make it really visible

 

[Stuart M]

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?

 

[Stuart M]

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?

 

[Stuart M]

Sorry I meant less than < DOH

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?

 

[Stuart M]

I'm being lazy I know but can you tell me how?

Thnaks. Probably the low-tec way might be best!

 

[Jacob Skaria]

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

 

[Patrick Molloy]

see Jacob's reply ... he gives the details

I'm being lazy I know but can you tell me how?

Thnaks. Probably the low-tec way might be best!