Creating an Alert Box

[Paul987]

I would like to create an Alert box that would pup up when a specific event occurs. For instance if cell A2 >1 , then a box would pop up, and state "a2 > 1" and require acknowledgement. Maybe even ding until click off. Is this possible? I would like to do it w/o VBA so it can be more flexible and can be created easier by an end user. Any advice?
TIA
Paul

[Bob Phillips]

Use Data Validation with either a number <= 1 or a custom formula of

=A2<=1

No ding though.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Paul987" <Paul987.24d4um_1141841400.8048@excelforum-nospam.com> wrote in
message news:Paul987.24d4um_1141841400.8048@excelforum-nospam.com...
>
> I would like to create an Alert box that would pup up when a specific
> event occurs. For instance if cell A2 >1 , then a box would pop up,
> and state "a2 > 1" and require acknowledgement. Maybe even ding until
> click off. Is this possible? I would like to do it w/o VBA so it can
> be more flexible and can be created easier by an end user. Any
> advice?
> TIA
> Paul
>
>
> --
> Paul987
> ------------------------------------------------------------------------
> Paul987's Profile:
http://www.excelforum.com/member.php...o&userid=24850
> View this thread: http://www.excelforum.com/showthread...hreadid=520206
>

[Paul987]

Thanks, Bob. I didn't even know that feature existed. It's people like you that keep this site running!

[Paul987]

New problem. The cell I was going to use the data validation on is real time data. Therefore, the user isn't entering a value into the cell, it's changing automatically and the validation doesn't work. Any ideas?

[Bob Phillips]

Here's another idea Paul.

Link a cell to the cell that gets update via the feed, with a simple =H10
say.

Add this code, which should trigger when the linked cell, A1 in my example,
goes below 1

Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "A1:H10"
Static oldvalue

If Me.Range(WS_RANGE) <> oldvalue Then
If Me.Range(WS_RANGE).Value <= 1 Then
MsgBox "Alert"
End If
End If
oldvalue = Me.Range(WS_RANGE).Value
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Paul987" <Paul987.24gvsz_1142016302.6544@excelforum-nospam.com> wrote in
message news:Paul987.24gvsz_1142016302.6544@excelforum-nospam.com...
>
> New problem. The cell I was going to use the data validation on is real
> time data. Therefore, the user isn't entering a value into the cell,
> it's changing automatically and the validation doesn't work. Any
> ideas?
>
>
> --
> Paul987
> ------------------------------------------------------------------------
> Paul987's Profile:
http://www.excelforum.com/member.php...o&userid=24850
> View this thread: http://www.excelforum.com/showthread...hreadid=520206
>

[Paul987]

Thanks, that worked well. Could you emplain to me why the WS_Range is defined as "A1:H10", I'm not sure why it wouldn't be just A1, since that is where the value its evaluating is.
Two more questions: 1) I would like to apply this to a list of values or two entire columns, for example Col A is the target value and Col B is the realtime value.
So when B1 <A1 then "Alarm" or when B9 < A9 then "Alarm" etc... Is there a way to easily do this?

2) If there a way to have the dialog box pop up on top of any open program? Or ding etc, I'm afraid the alerts wil go off, and I won't notice them..

Thanks for your help

[Paul987]

one more question: How do I stop it, so I can modify the code?