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
Bookmarks