VBA For Expiry date
Dear All
please any one can help me. If My Data Like NAME PASSPORT NO. ISSUED DATE PASSPORT EXPIRY DATE LOUIS HENNERY B125556 5/5/2004 5/5/2007 VICTOR H. S A125586 1/9/2005 1/9/2008 I want when I open this data file, MsgBox showing the list name of staff their passport is near or already expired. Thanks |
VBA For Expiry date
Private Sub Workbook_Open()
Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 1 To iLastRow 'iLastRow to 1 Step -1 If .Cells(i, "D").Value Date - 7 Then MsgBox .Cells(i, TEST_COLUMN).Value & _ " will/has expired on " & _ .Cells(i, "D").Text End If Next i End With End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alam" wrote in message ... Dear All please any one can help me. If My Data Like NAME PASSPORT NO. ISSUED DATE PASSPORT EXPIRY DATE LOUIS HENNERY B125556 5/5/2004 5/5/2007 VICTOR H. S A125586 1/9/2005 1/9/2008 I want when I open this data file, MsgBox showing the list name of staff their passport is near or already expired. Thanks |
All times are GMT +1. The time now is 10:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com