Cell warns of excessive value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ascesis - 26 Jul 2006 17:03 GMT

OK, my next problem.

I have a cell which calculates the total cost of 'costs' - obviously.

Now, I would like to know how to integrate a warning thing that wil
let the user know when the cost has exceeded a certain value.

For example, if the total cost currently stands at £1000 and then I ad
£2000 to 'cost' so that the total cost is now £3000, I should get
warning saying that this is too much, because the 'budget' is £2000

I've tried various things with the Data > Validation tool, but with n
success. If anyone can help me, it will be greatly appreciated

Reply to this Message

Bernie Deitrick - 26 Jul 2006 17:30 GMT

Ascesis,

Use Conditional Formatting instead. You can set the value at which it changes color to a set value,
or to another cell's value.

Use Format / Conditional Format... select "Cell Value" " is greater than" and enter 2000 or a
cell reference, then set the format to shade the cell red when the value is exceeded.

HTH,
Bernie
MS Excel MVP

> OK, my next problem.
>
[quoted text clipped - 9 lines]
> I've tried various things with the Data > Validation tool, but with no
> success. If anyone can help me, it will be greatly appreciated.

Reply to this Message

Ascesis - 27 Jul 2006 12:27 GMT

I had more success with the Conditional Formatting. Thanks a lot.

Signature

Ascesis

Reply to this Message

Ascesis - 27 Jul 2006 14:10 GMT

> Private Sub Worksheet_Calculate()
> If Range("Costs").Value > 2000 Then
> MsgBox "The budget is only 2000"
> End If
> End Sub

Is there something in this that I will have to change so that it work
on my worksheet?

I get a debug error when I paste this code in, and the code edito
highlights "If Range("Costs").Value > 2000 Then" in yellow. Hoverin
the cursor over this reveals something like, 'Method range o
object_worksheet failed'

Reply to this Message

Bernie Deitrick - 27 Jul 2006 15:17 GMT

You have to name a cell Costs .

But note that this code will flash a message everytime you calculate, if the value is over 2000.
Better to limit the effect by using the Change event, and checking to see if the cells being summed
have been changed prior to giving the message.

HTH,
Bernie
MS Excel MVP

>> Private Sub Worksheet_Calculate()
>> If Range("Costs").Value > 2000 Then
[quoted text clipped - 9 lines]
> the cursor over this reveals something like, 'Method range of
> object_worksheet failed'.

Reply to this Message

Ascesis - 28 Jul 2006 11:37 GMT

yeah, this isn't going too well.

I think a message box is a superfluity for my work, anyway.

Just interested.

I'll take out one of those thick Excel guides from the library soon

:cool

Reply to this Message

Excelenator - 26 Jul 2006 17:33 GMT

You could place the following in the worksheet calculation event in the
VB editor

Code:
--------------------
Private Sub Worksheet_Calculate()
If Range("Costs").Value > 2000 Then
MsgBox "The budget is only 2000"
End If
End Sub
--------------------

This would give you the message box above if the total surpasses the
limit you set.

Signature

Excelenator

Reply to this Message