Macro with Message Box for Date Greater Than Today

rhmkrmi

Board Regular
Joined
Aug 17, 2012
Messages
164
Hi there, I need a VBA to be used in Excel to check a couple of columns (F:F, H:H, N:N) to see if any cell in those columns contain a date that is greater than today and pop up a message. Could you please help?

Thank you.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
If Application.WorksheetFunction.Max(Columns("F"), Columns("H"), Columns("N")) > Date Then MsgBox "a message"
 

rhmkrmi

Board Regular
Joined
Aug 17, 2012
Messages
164
Thanks Fazza.

I am using a text format for my dates (TEXT(F10,"dd-mmm-yyyy")).
How can the code be changed to pick up this format as well as a true date format?

Thank you.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
That isn't a text format: it is text. Not a date as far as Excel is concerned. The code doesn't need changing.

The formula refers to column F so column F presumably really does contain a date. And again the code should be fine.

The code is independent of formats. For example, enter 44444 in a cell. It can be formatted (CTRL-1) as number like 44,444 or a date like 5-Sep-21

However using a TEXT formula returns text.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
PS,

If you have text instead of dates here are some ways to convert them to numbers so Excel handles them as dates,
=value(a1)
=a1*1
=a1+0
 
Last edited:

rhmkrmi

Board Regular
Joined
Aug 17, 2012
Messages
164
Thanks a lot for all the tips. I will try them.

In the meantime, can I also ask how I can use the code that you provided in Sheet1 but reference it to the same columns in Sheet2?

In Sheet2 I have dates but in Sheet1 I have converted them to text because that especial format is required.

Thanks again.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
you can add the reference to Sheet2

if Sheet2 is the code name

If Application.WorksheetFunction.Max(Sheet2.Columns("F"), Sheet2.Columns("H"), Sheet2.Columns("N")) > Date Then MsgBox "a message"
 
Last edited:

rhmkrmi

Board Regular
Joined
Aug 17, 2012
Messages
164
Thank you.

I am using this but it pops up a message when I enter a future date in any field!

Private Sub Worksheet_Change(ByVal Target As Range)


If Application.WorksheetFunction.Max(Columns("F"), Columns("H"), Columns("N")) > Date Then MsgBox "Please check your dates"


End Sub

What am I doing wrong?

Thank you.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
I can't tell. I don't understand exactly what you've done.

What do you think it is?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
Maybe there is already - before the data entry that triggers the macro - a future date in one of the columns of interest?
 

Forum statistics

Threads
1,078,367
Messages
5,339,783
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top