PDA

View Full Version : Create an alert popup



Ike
11-08-2011, 04:33 PM
Hello All,

I am looking for a excel vba that would automatically provide an alert popup when data is pasted onto a sheet

named "Orders" where the following 3 conditions are true...

1. There is a specific phrase in column C Phrase is - 2) Replacement
2. The same row where the phrase exists should have yesterday's date in column B
3. The same row will have a blank cell in column F

Thankyou in advance,

Ike

shacho
11-09-2011, 07:39 AM
This may seem a little verbose, but it accounts for a lot of conditions you might not anticipate, like whether the user is pasting merged data, or if the "blank" cell is empty or has spaces, etc.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells(1).Column = 3 Then TestForAlert Target.Cells(1)
End Sub

Private Sub TestForAlert(Target As Range)
Dim MatchesPattern As Boolean

MatchesPattern = False
If Target.Value <> "2) Replacement" Then GoTo DONE
If IsDate(Target.Offset(0, -1).Value) = False Then GoTo DONE
If CLng(Target.Offset(0, -1).Value) <> CLng(Date) - 1 Then GoTo DONE
If Trim(Target.Offset(0, 3).Value) <> "" Then GoTo DONE
MatchesPattern = True

DONE:
If MatchesPattern Then
MsgBox "User has pasted the matching patttern into " & Target.Address & "."
End If
End Sub