popup alert message during the worksheet open and continuous work on the sheet popup jump

[shailkam2001]

Sir I want to VBA code for worksheet of excel one column as Column C2 to C10 have a any future date. this Column(C2 to C10) date compare from system date and given popup message before 10 days, 20 days..... as date mention in Column C2 is 14/06/2014 and today system date 04/06/2014 so that difference is 10 days now given the automatic popup message when open the worksheet of excel and this popup message given every 5 mins when worksheet is open, but expire 10 days as column C2 have a date is 03/06/2014 and today is system date is 04/06/2014 then difference is -1 day now given the message is expired the date with remaining 10 days message because some condition are true and some condition are false in the same worksheet of excel. given this code not met two condition one is not popup every 5 secs and second condition is not met -1, -2 etc expired date as given the message should be expired the date before 1 or 2 days etc. so please reply as early. given my vba code below

Private Sub Workbook_Open()
Dim Mycell
Dim Rng
Dim strText As String
Dim nTime
Set Rng = Sheets("Sheet1").Range("C2:C16")
For Each Mycell In Rng
If Mycell.Value <= 10 Then
strText = strText & vbLf & " Due Days " & " = " & Mycell.Offset(0, 0).Value & " " & " Tender Open date" & " = " & Date + Mycell.Value
End If
Next Mycell

If Len(strText) > 0 Then
nTime = Now + TimeSerial(0, 0, 5) 'every 5 secs
MsgBox strText & vbLf & "Are Overdue. Take Action Now! ", vbOKOnly Or vbExclamation, "Tasks Overdue"
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Mycell
Dim Rng
Dim strText As String

Set Rng = Sheets("Sheet1").Range("C2:C16")
For Each Mycell In Rng
If Mycell.Value <= 10 Then
strText = strText & vbLf & " Due Days " & " = " & Mycell.Offset(0, 0).Value & " " & " Tender Open date" & " = " & Date + Mycell.Value

End If
Next Mycell

If Len(strText) > 0 Then
MsgBox strText & vbLf & "Are Overdue. Take Action Now! ", vbOKOnly Or vbExclamation, "Tasks Overdue"
End If
End Sub

Thanks. Shailendra Singh

[arlu1201]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.