Message box help
 

I would like to have a message box appear when the value of a cell is
greater than what I have determined, then place a statement in a cell
to show a message. For example; if the value of a cell is greater that
$600.00, then the message box would pop up and state "Supervisor
Approval Needed". Then it would place a line on the sheet that states
"Supervisor contacted" in the cell of my choosing.

Thanks,

Michael

Message box help
 

Michael, try this,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value 600 Then
MsgBox "Supervisor Approval Needed"
[A2] = "Supervisor contacted"
End If
Application.EnableEvents = True
End Sub

this is worksheet code, right click on the sheet tab, view code to put it in

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"hedgehog1" wrote in message
ps.com...
I would like to have a message box appear when the value of a cell is
greater than what I have determined, then place a statement in a cell
to show a message. For example; if the value of a cell is greater that
$600.00, then the message box would pop up and state "Supervisor
Approval Needed". Then it would place a line on the sheet that states
"Supervisor contacted" in the cell of my choosing.

Thanks,

Michael

Message box help
 

Hi Michael,

Try:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rngNote As Range
Const msg As String = "Supervisor Approval Needed"
Const sstr As String = "Supervisor contacted"

Const triggerVal As Double = 600 '<<==== CHANGE

Set rng = Me.Range("A1:A20") '<<==== CHANGE
Set rngNote = Me.Range("B1") '<<==== CHANGE

If Not Intersect(rng, Target) Is Nothing Then
If Target.Value triggerVal Then
MsgBox Prompt:=msg, _
Buttons:=vbCritical, _
Title:="Threshold Exceeded!"
Application.EnableEvents = False
rngNote.Value = sstr
Application.EnableEvents = True
End If
End If
End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman


"hedgehog1" wrote in message
ps.com...
I would like to have a message box appear when the value of a cell is
greater than what I have determined, then place a statement in a cell
to show a message. For example; if the value of a cell is greater that
$600.00, then the message box would pop up and state "Supervisor
Approval Needed". Then it would place a line on the sheet that states
"Supervisor contacted" in the cell of my choosing.

Thanks,

Michael