alert / pop-up window when sheet is activated, and today's date is 3 months or less before listed in cells of column E

[ruchi613]

Hello,
I have an Inventory Workbook with 63 sheets (each sheet keeps inventory entries for an individual item pallet by pallet: quantity delivered for each pallet, licence plate# for each pallet, lot#, expiration date, quantity picked up).
For this particular problem, I only need to work with expiration date (column E), licence plate# (column F) and quantity picked up (column G).
I'd like to create an alert, that would be triggered when each sheet is activated, as a pop-up window with a message "approaching an expiration date for licence#....". Code should check column E to become less than 3 months before expiration date, then if column G is empty (meaning this pallet was not picked up yet), create that alert, that would be able to read and display an appropriate licence# from column F.

I am completely new to programming, but would love to learn. Any help, suggestions would be great.

 

[svendiamond]

Could you share your workbook? Or, ideally, use the Mr. Excel HTML Maker found here: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

It will allow you to copy and paste small snippets of your workbook as HTML code which will make it super easy for us to help you. It will basically allow you to show your workbook like this:

Book1
	A	B	C
1	Date	Number	Name
2	5-Aug	1	John
3	6-May	2	Sara
4	9-Jun	222	Sue
Sheet2

 

[ruchi613]

Could you share your workbook? Or, ideally, use the Mr. Excel HTML Maker found here: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

It will allow you to copy and paste small snippets of your workbook as HTML code which will make it super easy for us to help you. It will basically allow you to show your workbook like this:

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #9BC2E6, align: center"]Date[/TD]
[TD="bgcolor: #9BC2E6, align: center"]Number[/TD]
[TD="bgcolor: #9BC2E6, align: center"]Name[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]5-Aug[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]John[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]6-May[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Sara[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]9-Jun[/TD]
[TD="align: center"]222[/TD]
[TD="align: center"]Sue[/TD]

</tbody>

Sheet2​

[ Here is a sample of the spreadsheet:
[TABLE="width: 793"]
<tbody>[TR]
[TD]In[/TD]
[TD]Market[/TD]
[TD]Qty[/TD]
[TD]LOT#[/TD]
[TD]EXP.DATE[/TD]
[TD]LICENSE PLATE[/TD]
[TD]Qty Ordered[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2015[/TD]
[TD="align: right"]1.5913[/TD]
[TD]65[/TD]
[TD]15001[/TD]
[TD]11/11/2016[/TD]
[TD]1004341652[/TD]
[TD]65[/TD]
[TD="align: right"]2/17/2016[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2015[/TD]
[TD="align: right"]1.5913[/TD]
[TD]65[/TD]
[TD]15001[/TD]
[TD]11/11/2016[/TD]
[TD]1004341696[/TD]
[TD]65[/TD]
[TD="align: right"]2/23/2016[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2015[/TD]
[TD="align: right"]1.5913[/TD]
[TD]65[/TD]
[TD]15001[/TD]
[TD]11/11/2016[/TD]
[TD]1004341700[/TD]
[TD]65[/TD]
[TD="align: right"]2/26/2016[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2015[/TD]
[TD="align: right"]1.5913[/TD]
[TD]65[/TD]
[TD]15001[/TD]
[TD]11/11/2016[/TD]
[TD]1004341792[/TD]
[TD]65[/TD]
[TD="align: right"]3/15/2016[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2015[/TD]
[TD="align: right"]1.5913[/TD]
[TD]65[/TD]
[TD]15001[/TD]
[TD]11/11/2016[/TD]
[TD]1004341798[/TD]
[TD]65[/TD]
[TD="align: right"]3/25/2016[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2015[/TD]
[TD="align: right"]1.5913[/TD]
[TD]65[/TD]
[TD]15001[/TD]
[TD]11/11/2016[/TD]
[TD]1004341872[/TD]
[TD]65[/TD]
[TD="align: right"]3/30/2016[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2015[/TD]
[TD="align: right"]1.5913[/TD]
[TD]65[/TD]
[TD]15001[/TD]
[TD]11/11/2016[/TD]
[TD]1004341978[/TD]
[TD]65[/TD]
[TD="align: right"]4/1/2016[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2015[/TD]
[TD="align: right"]1.5913[/TD]
[TD]69[/TD]
[TD]15001[/TD]
[TD]11/11/2016[/TD]
[TD]1004342023[/TD]
[TD]69[/TD]
[TD="align: right"]4/6/2016[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]1.5999[/TD]
[TD]65[/TD]
[TD]15512[/TD]
[TD]12/2/2016[/TD]
[TD]1004398733[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]1.5999[/TD]
[TD]65[/TD]
[TD]15512[/TD]
[TD]12/2/2016[/TD]
[TD]1004398924[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2015[/TD]
[TD="align: right"]1.5999[/TD]
[TD]65[/TD]
[TD]15512[/TD]
[TD]12/2/2016[/TD]
[TD]1004399013[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
]

 

[svendiamond]

So let me get this straight. Upon activation of the sheet, you want a message box to appear IF and only IF there is a license that is due to expire less than three months from today AND the "Qty Ordered" is blank. Is this correct? So in your example table there would be NO message box, correct? Since the next one expiring is more than three months away. Is this correct?

What EXACTLY would your desired result be for the following table:

Book1
	A	B	C	D	E	F	G	H
1	In	Market	Qty	LOT#	EXP.DATE	LICENSE PLATE	Qty Ordered	Date
2	11/24/2015	1.5913	65	15001	9/9/2016	1004341652		2/17/2016
3	11/24/2015	1.5913	65	15001	9/9/2016	1004341696	65	2/23/2016
4	11/24/2015	1.5913	65	15001	10/4/2016	1004341700		2/26/2016
5	11/24/2015	1.5913	65	15001	11/11/2016	1004341792	65	3/15/2016
6	11/24/2015	1.5913	65	15001	11/11/2016	1004341798	65	3/25/2016
7	11/24/2015	1.5913	65	15001	11/11/2016	1004341872	65	3/30/2016
8	11/24/2015	1.5913	65	15001	11/11/2016	1004341978	65	4/1/2016
9	11/24/2015	1.5913	69	15001	11/11/2016	1004342023	69	4/6/2016
10	12/12/2015	1.5999	65	15512	12/2/2016	1004398733
11	12/12/2015	1.5999	65	15512	12/2/2016	1004398924
12	12/12/2015	1.5999	65	15512	12/2/2016	1004399013
Sheet1

 

[ruchi613]

You're absolutely right.
Sorry I didn't give you a proper example.
So in your example, pop-up alert should notify me that license plate##
1004341652 and 1004341700 are due to expire in less than 3 months.
As much as I try to watch for dates and pull out product according to the expiration dates, I've just discovered a pallet with 38 cases of expired product. Really would like to avoid it in the future.
Thank you for taking your time to figure it out.

Another option would be to have product within 3 months of expiration (from entire workbook) to be spooled into dedicated worksheet in the same file (maybe macro that I could run weekly or autorunning every time file is open?).
Not sure which way would be more realistic to execute.

 

[svendiamond]

Let's try the first choice first and see if it works for you.

Right-click on the sheet name/tab and click "View Code" then paste in this code:

Private Sub Worksheet_Activate()


Dim lastRow As Long, i As Long, daysLeft As Long, qtyOrder As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row


With ActiveSheet
    For i = 2 To lastRow
        daysLeft = .Cells(i, "E") - Date
        qtyOrder = .Cells(i, "G")
            If daysLeft <= 90 And qtyOrder = 0 Then
                MsgBox "License #" & .Cells(i, "F") & " expires in " & daysLeft & _
                    " days on " & .Cells(i, "E"), vbOKOnly, "License expiring soon!"
            End If
    Next i
End With


End Sub

 

[ruchi613]

Hi,
I ran your code, and it does recognize that there is short dated product in activated sheet. But does not show license# correctly.
It shows the following message first: "License # expires in -42586 days on", and after I click "OK", takes me back to the code that has an error messg "Run-time error '13': type mismatch", and after debugging it highlights the line inside the loop " daysLeft = .Cells(i, "E") - Date".

Does it have anything to do with format of the cells?