Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replace VBA run-time error message with custom message | Excel Programming | |||
Replace Excel Message w/Custom Message | Excel Programming | |||
Intercept/replace standard 'cell protected' message with my own message? | Excel Programming | |||
Opening an attachment of a message that has a message as attachmen | Excel Programming | |||
Displaying a message in a message box without requiring user to click anything to proceed | Excel Programming |