Hi,
I am trying to come up with a macro that will display a pop up box
when certain criteria are met. The user will enter a date in cell D2
and a zip code in cell D3 that will then automatically display a date
in cell G3 via vlookup. So once these two cells are populated by the
user I need this macro to run and do the following: If the date in
cell G3 is after the date in cell D2, I want a pop up message box to
say that the date is in the future. I've tried formulas and
conditional formatting to warn users, but they ignore these, so I need
a pop up that they would have to click OK on to exit out of to
hopefully get the message across. Thank you for any help.

Right click on sheet tab, view code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
'Only run if change made to D3, and D2 and D3 are not = ""
If Intersect(Target, Range("D3")) Is Nothing Or _
Range("D2").Value = "" Or Range("D3").Value = "" Then Exit Sub

If Range("G3").Value Range("D2").Value Then
MsgBox "You are in the future!", vbOKOnly, "WARNING!"
End If

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jenny" wrote:

Hi,
I am trying to come up with a macro that will display a pop up box
when certain criteria are met. The user will enter a date in cell D2
and a zip code in cell D3 that will then automatically display a date
in cell G3 via vlookup. So once these two cells are populated by the
user I need this macro to run and do the following: If the date in
cell G3 is after the date in cell D2, I want a pop up message box to
say that the date is in the future. I've tried formulas and
conditional formatting to warn users, but they ignore these, so I need
a pop up that they would have to click OK on to exit out of to
hopefully get the message across. Thank you for any help.

Jenny
This macro will fire when the contents of D3 is changed. It will then
look at G3 and D2. If G3 is greater than D2, a message box will pop up
telling the user "The date is in the future." and a noise will sound to
attract his attention. The box will stay there until he clicks on the OK
button. Note that this macro is a Worksheet event macro and must be placed
in the sheet module of the pertinent sheet. You access that module by
right-clicking on the sheet tab and selecting View Code. Paste this macro
into that module. "X" out of the module to return to your sheet. HTH
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("D3")) Is Nothing Then
If Range("G3") Range("D2") Then
MsgBox "The date is in the future.", 16, "READ THIS"
End If
End If
End Sub

"Jenny" wrote in message
...
Hi,
I am trying to come up with a macro that will display a pop up box
when certain criteria are met. The user will enter a date in cell D2
and a zip code in cell D3 that will then automatically display a date
in cell G3 via vlookup. So once these two cells are populated by the
user I need this macro to run and do the following: If the date in
cell G3 is after the date in cell D2, I want a pop up message box to
say that the date is in the future. I've tried formulas and
conditional formatting to warn users, but they ignore these, so I need
a pop up that they would have to click OK on to exit out of to
hopefully get the message across. Thank you for any help.

This is perfect! It works as I want.

Just one question, if I wanted it to run every time cell D2 or D3 are
modified, how do I do that?

Both macros work great. Thank you so much!