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 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
--
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 (E-Mail Removed)> wrote in message
news:2234ADA5-741C-41EB-9772-(E-Mail Removed)...
> 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 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
> --
> 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 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
> > --
> > 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 (E-Mail Removed)> wrote in message
> news:2234ADA5-741C-41EB-9772-(E-Mail Removed)...
> > 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 Format>Conditional Formatting.

Condition1
CEll value is>lessthan>100
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 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
> > > --
> > > 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" <(E-Mail Removed)> wrote in message
news:2FB11423-DF8D-43BC-BE8D-(E-Mail Removed)...
> 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 (E-Mail Removed)> wrote in message
>> news:2234ADA5-741C-41EB-9772-(E-Mail Removed)...
>> > 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
>>