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

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