Pop Up error message

[maratonomak]

Hi,

I'm trying to create a pop up message based on the age. Let say I enter the date of birth in cell C9 and the age is calculated in Cell C10. Based on the age, <30 and >80 I need a pop up message . Minimum age requirement are not met for <30 and Maximum age requirement are not met for >80. Thanks in advance.

 

[Smitty]

Welcome to the Board!

How's this:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("C9")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Offset(1).Value < 30 <SPAN style="color:#00007F">Or</SPAN> Target.Offset(1).Value > 80 <SPAN style="color:#00007F">Then</SPAN><br>                MsgBox "Minimum age requirement are not met for <30 and Maximum age requirement are not met for >80"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[maratonomak]

I'm not sure which option would be best, Data validation or VBA code .

 

[maratonomak]

Thank you for your prompt response.

I was looking to have a separate msg box for <30 (minimum age not met) and a separate pop up msg for > 80 (max age not met).

Please see below.

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("C9")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (Age Ok)
If Target.Offset(1).Value < 30
MsgBox "Minimum age requirement are not met "
If Target.Offset(1).Value > 80 Then
MsgBox "Maximum age requirement are not met "
End If
End Sub

 

[maratonomak]

I apologize for not pointing this in the beginning, but if I do want the same pop up msg for another cell (ex C18) ?

Thanks again !

 

[Smitty]

I apologize for not pointing this in the beginning, but if I do want the same pop up msg for another cell (ex C18) ?

Set rng = Target.Parent.Range("C9, C18")

 

[maratonomak]

Set rng = Target.Parent.Range("C9, C18")

Thank you so much.

 

[maratonomak]

Set rng = Target.Parent.Range("C9, C18")

Hi,
Sorry bothering again,

Is there any way to add some other criteria for two more cells (C12 and C20) if > $300,000, to have the warning message as well. Thanks in advance.

 

[Smitty]

Sure:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br> <SPAN style="color:#007F00">' Code goes in the Worksheet specific module</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br> <SPAN style="color:#007F00">' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("C9, C12, C18, C20")<br> <SPAN style="color:#007F00">' Only look at single cell changes</SPAN><br> <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <SPAN style="color:#007F00">' Only look at that range</SPAN><br> <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <SPAN style="color:#007F00">' Action if Condition(s) are met (Age Ok)</SPAN><br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Address<br>        <SPAN style="color:#00007F">Case</SPAN> "$C$9", "$C$18"<br>            <SPAN style="color:#00007F">If</SPAN> Target.Offset(1).Value < 30 <SPAN style="color:#00007F">Then</SPAN><br>                MsgBox "Minimum age requirement are not met "<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Offset(1).Value > 80 <SPAN style="color:#00007F">Then</SPAN><br>                MsgBox "Maximum age requirement are not met "<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Case</SPAN> "$C$12", "$C$20"<br>            <SPAN style="color:#00007F">If</SPAN> Target.Value > 300000 <SPAN style="color:#00007F">Then</SPAN><br>                MsgBox "Greater than $300,000"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[maratonomak]

Thanks again for your help