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

"Stuart M" wrote:

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 :)

Somthing like the below. Right click sheet tabView 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
--
If this post helps click Yes
---------------
Jacob Skaria


"Stuart M" wrote:

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 :)

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

"Stuart M" <Stuart wrote in message
...
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 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?

"Mike H" wrote:

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

"Stuart M" wrote:

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 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?

"Jacob Skaria" wrote:

Somthing like the below. Right click sheet tabView 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
--
If this post helps click Yes
---------------
Jacob Skaria


"Stuart M" wrote:

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 :)

Sorry I meant less than < DOH

"Stuart M" wrote:

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?

"Jacob Skaria" wrote:

Somthing like the below. Right click sheet tabView 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
--
If this post helps click Yes
---------------
Jacob Skaria


"Stuart M" wrote:

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 :)

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

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

"Patrick Molloy" wrote:

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

"Stuart M" <Stuart wrote in message
...
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 :)

Select the sum cell. From menu FormatConditional Formatting.

Condition1
CEll value islessthan100
Select a color from 'Format'

If this post helps click Yes
---------------
Jacob Skaria


"Stuart M" wrote:

Sorry I meant less than < DOH

"Stuart M" wrote:

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?

"Jacob Skaria" wrote:

Somthing like the below. Right click sheet tabView 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
--
If this post helps click Yes
---------------
Jacob Skaria


"Stuart M" wrote:

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 :)

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

"Stuart M" wrote in message
...
I'm being lazy I know but can you tell me how?

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

"Patrick Molloy" wrote:

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

"Stuart M" <Stuart wrote in message
...
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 :)