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

--
Ascesi
-----------------------------------------------------------------------
Ascesis's Profile: http://www.excelforum.com/member.php...fo&userid=3680
View this thread: http://www.excelforum.com/showthread.php?threadid=56522

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


"Ascesis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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 will
> 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 add
> £2000 to 'cost' so that the total cost is now £3000, I should get a
> warning saying that this is too much, because the 'budget' is £2000
>
> I've tried various things with the Data > Validation tool, but with no
> success. If anyone can help me, it will be greatly appreciated.
>
>
> --
> Ascesis
> ------------------------------------------------------------------------
> Ascesis's Profile: http://www.excelforum.com/member.php...o&userid=36801
> View this thread: http://www.excelforum.com/showthread...hreadid=565221
>

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.


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=565221

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


--
Ascesis
------------------------------------------------------------------------
Ascesis's Profile: http://www.excelforum.com/member.php...o&userid=36801
View this thread: http://www.excelforum.com/showthread...hreadid=565221

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

--
Ascesi
-----------------------------------------------------------------------
Ascesis's Profile: http://www.excelforum.com/member.php...fo&userid=3680
View this thread: http://www.excelforum.com/showthread.php?threadid=56522

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


"Ascesis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
>> 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 works
> on my worksheet?
>
> I get a debug error when I paste this code in, and the code editor
> highlights "If Range("Costs").Value > 2000 Then" in yellow. Hovering
> the cursor over this reveals something like, 'Method range of
> object_worksheet failed'.
>
>
> --
> Ascesis
> ------------------------------------------------------------------------
> Ascesis's Profile: http://www.excelforum.com/member.php...o&userid=36801
> View this thread: http://www.excelforum.com/showthread...hreadid=565221
>

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

--
Ascesi
-----------------------------------------------------------------------
Ascesis's Profile: http://www.excelforum.com/member.php...fo&userid=3680
View this thread: http://www.excelforum.com/showthread.php?threadid=56522