Display alert when target date is less than current date

[rajagopal]

Hi,
I've an excel file which has a column - Target date. I want a alert display message to be shown when the user opens the document, the condition being target date is less than the current date.

I've calendar user form which will be shown when the user place the cursor in the cell range H7:J3173. I want the ESC key to be enabled i.e. when the user clicks Esc key, the calendar has to disappear.

I attach the file for your ref.

bye
Raj

[Bob Phillips]

Just set the Cancel property of the Cancel button to True.

[rajagopal]

That is fine.
What about my other requirement - I've an excel file which has a column - Target date. I want a alert display message to be shown when the user opens the document, the condition being target date is less than the current date.

Raj

[Bob Phillips]

Exactly, you have a column, what if there are many less than?

[rajagopal]

Irrespective of the nos. a single alert message will do.

[rajagopal]

Can anyone help with my requirement?

[Bob Phillips]

Private Sub Workbook_Open()
Dim i As Long

With Worksheets("Action item tracker")

For i = 2 To .Cells(.Rows.Count, "I").End(xlUp).Row

If .Cells(i, "I").Value < Date Then

MsgBox "Dates less than today"
Exit For
End If
Next i
End With
End Sub

[rajagopal]

Can you give explain me the codes:
For i = 2 To .Cells(.Rows.Count, "I").End(xlUp).Row

If .Cells(i, "I").Value < Date Then

[rajagopal]

The alert message should not be displayed when there is no items in the Target date column(Column H)..

Alert message has to be displayed when the date given in the column H is less than today date.

[Bob Phillips]

Can you give explain me the codes:
For i = 2 To .Cells(.Rows.Count, "I").End(xlUp).Row

If .Cells(i, "I").Value < Date Then

The first line calculates the last row of data and runs a loop from row 2 to that last line.

The second just compares the value of the current cell in the loop against today's date.

[rajagopal]

I want this alert to be triggered only when any of the action item having target date is less than today's date.
Right now, the code displays the alert message even in the blank tempalte.

Can you also look into the post
http://vbaexpress.com/forum/showthread.php?t=23232

[Bob Phillips]

Private Sub Workbook_Open()
Dim i As Long

With Worksheets("Action item tracker")

For i = 6 To .Cells(.Rows.Count, "I").End(xlUp).Row

If .Cells(i, "I").Value < Date Then

MsgBox "Dates less than today"
Exit For
End If
Next i
End With
End Sub