VBA popup box warning of expiration date

Watchdawg

Board Regular
Joined
Jan 21, 2015
Messages
84
Hello guru's! I'm trying to get a popup window to show when a date is entered that is less than 8 months from today's date.
I also need it to send out the alert when the spreadsheet is opened.
Basically, Column A is the part number, Column B is the lot number and Column C is the expiration date. If the user enters a date into column C (which is currently 57 lines long, but could be as many as 200) and it's less than 8 months out, it displays "Part A, Lot B is within the 8 month requirement". They should be able to close the window out in case it's going to be accepted anyway. Therefor, once the workbook is opened again, it needs to check for expiration dates that are already populated for the same criteria and display a list of parts/lots that are out of tolerance.
And just because I can't make it simple, if the expiration date doesn't have anything entered, it shouldn't throw out the warning, just move on to the next populated cell.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Can you please help me understand:
less than 8 months from today's date.

So are you saying 5/17/2014 would be:
less than 8 months from today's date.

Give me a example of what would and would not apply
 
Upvote 0
For example, lets say the spreadsheet is opened on 7/1/2018. If the expiration date entered is 3/1/2019 or earlier, it's below the 8 month threshold.
 
Upvote 0
Well this part should do the first part of what you want.
If a date is entered in column C that is out of date you will get a message box.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The script will activate when you enter a value in column C
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 6/19/18 10:00 PM EDT
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Value < DateAdd("M", "8", Date) Then
        ans = "Part Number " & Target.Offset(, -2).Value
        anss = "Lot Number " & Target.Offset(, -1).Value
        MsgBox ans & vbNewLine & anss & vbNewLine & "Is within the 8 month requirement"
    End If
End If
End Sub
 
Upvote 0
Now this part I do not understand:
They should be able to close the window out in case it's going to be accepted anyway.
Close what window. This is a message Box window.
You must click OK to continue.
If your not wanting a message Box then i don't know what you mean when you say Pop up Window.

Then you said:
Therefor, once the workbook is opened again, it needs to check for expiration dates that are already populated for the same criteria and
display a list of parts/lots that are out of tolerance.
<strike></strike>

<strike></strike>
Display a list where?

See I always need specific details. Saying display a list is not specific.
 
Upvote 0
Sorry, I didn't even think about the user having to press "OK" to release the message box, so that'll work just fine. For now I can live without the list, but what I was saying that if the user has entered 30 parts and populated 30 expiration dates in column C (let's say 6 were within the 8 month window), then when the workbook is opened again (in this case by another user), it will display the 6 parts that are within the 8 month window.
That said, I pasted your code to try it out and nothing at all happened...
 
Upvote 0
So your saying you entered a date like 8/8/2018 into column C and nothing happened.
And you installed the code like I mentioned:
Like this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
This part of the script only runs when you enter a date like 8/8/2018 into column C
 
Upvote 0
I never click on links or open other workbooks or files.

Try this and tell me what happens when you enter any value in column C

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 6/20/18 10:15 AM EDT
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    MsgBox "This works"
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top