nikki -

while a pop-up message box is good, if the user can just click "ok" &
then go merrily along their way, what good is it?

somtimes for something like this i prefer a formatted merged-cell box
off to the side that shows up with conditional formatting when a value
goes higher than it's supposed to. that way it doesn't disappear
UNTIL they fix the problem.

for instance, in a worksheet i designed for my boss, in one biggish
merged cell that is VISIBLE TO THE USER i put the following formula

=IF((SUM(C3:C13)6),"WARNING! THERE ARE ONLY SIX UNITS TO
DISTRIBUTE!","")

then i put in the conditional formatting:

if cell value is equal to ="WARNING! THERE ARE ONLY
SIX UNITS TO DISTRIBUTE!" formatting (i chose black background w/
red text - very noticeable)

so in playing with her numbers, if she goes over 6 units, that black
"box" with red text pops up, & won't go away until she fixes the
problem.

just an idea
:)
susan



On May 24, 8:36 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Nikki

Is the value a calculated value or typed in?

The Change code is triggered by typing, editing or copying to that cell.

Re-calculation will not trigger a change event.

My skills don't extend to making a pop-up using Calculate event code.

Wait for a better answer if you need a Calculate event

Gord

On Thu, 24 May 2007 17:06:01 -0700, Nikki
wrote:



Hi,

Thanks. I changed myrange to I10:I15 but no message pops up.

"Gord Dibben" wrote:

Nikki

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const myrange As String = "A1:A10"
On Error GoTo stoppit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(myrange)) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value 50000 Then
MsgBox "Have you completed the forward cover form?"
End If
End If
stoppit:
Application.EnableEvents = True
End Sub

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

myrange can be changed to suit.

Gord Dibben MS Excel MVP

On Thu, 24 May 2007 14:41:00 -0700, Nikki
wrote:

Thanks that formula works but I want a box to pop up that the user needs to
click ok to acknowledge it instead of it just being written in a cell.

Any ideas?

"Equiangular" wrote:

=If(A250000,"put the text here","")

Nikki wrote:
Hi,

I have a spreadsheet that I would like an message to appear that says 'Have
you completed the forward cover form' if the value in the cell equals more
than $50,000. Is there some code for this?

Thanks- Hide quoted text -

- Show quoted text -