Pop Up Message

[chickalina]

I want to create a popup message for orders over a certain amount. We can
generally process purchase orders up to a certain value without a quote.
Anything over that I want a reminder that they need to email the quote to the
person entering the requisition. It's Column G in my spreadsheet.
Thanks for any help!
M

 

[Mike H]

Hi,

The easiest way to do this would be using the worksheet change event but if
Column G is a calculated amount then that wouldn't call the sheet change
event code. Is G a calculation or is it manually entered. If calculated is
there another cell that is manually entered that could be used?

Mike

 

[chickalina]

This cell is manually entered. If an amount is entered manually by the user
into the cell and it's over $5,000, I want a pop up reminder.
Thanks.

 

[Mike H]

Hi,

Right click the sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("G:G")) Is Nothing Then
Application.EnableEvents = False
If Target.Value > 5000 Then
response = MsgBox("You must email a quote for an amount of " &
Target.Value, vbCritical)
End If
Application.EnableEvents = True
End If
End Sub

Mike

 

[chickalina]

How would that fit into what I already have?
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("A:A")) Is Nothing Then
On Error GoTo endit
Application.EnableEvents = False
If IsNumeric(Target) Then
Target.Offset(0, 8).Value = Format(Date, "mm/dd/yyyy")
DueDate = Date + Day(7)
Target.Offset(0, 9).Value = DueDate
Target.Offset(0, 7).Value = Environ("username")
End If
End If
endit:
Application.EnableEvents = True
End Sub