macro for excel msg pop up when certain conditions are met
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. |
macro for excel msg pop up when certain conditions are met
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. |
macro for excel msg pop up when certain conditions are met
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. |
macro for excel msg pop up when certain conditions are met
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? |
macro for excel msg pop up when certain conditions are met
Both macros work great. Thank you so much!
|
All times are GMT +1. The time now is 04:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com