display a message if a cell value is highe than $50,000

[Guest]

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

 

[Equiangular]

=If(A2>50000,"put the text here","")

 

[Guest]

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?

 

[Gord Dibben]

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

 

[Guest]

Hi,

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

 

[Gord Dibben]

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

 

[Susan]

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