Function or Macro

[VBA Noob]

Hi,

I'm using the below formula to check if the User has entered a valid date format e.g 30/09/06 is valid but 30/0906 isn't as will as checking I check that the the tab name which is always a month and year e.g September06 is always equal the Tab year.

It's there a way to shorten the code or could it be converted into a macro to check each sheet.

PS. I know I could use data validation to control user enter but wanted to avoid that if possible

=IF(TEXT(YEAR(D2),"####")="20"&RIGHT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,99),2)=TRUE,"","Wrong year !!")&IF(ISERROR(AND(ISNUMBER(D2),OR(CELL("Format",D2)={"D1","D2","D3","D4","D5"}))),"","<<< Error Check date format")

VBA Noob

[Suamere]

Before I throw random answers, riddle me this...
Where do they enter the date, and where is it displayed?

If they enter the date where that function is, it will delete the function. If they enter the date somewhere else and that function checks it, the wrong date will still be somewhere else. There is a very simple way to do what I think you are doing with VB if you answer this right...

[VBA Noob]

The Cell with the date is D2 down approx 300 rows to say D300 the Formula is in Y2 going down.

D1 has text & a Formula. If Y2 to Y300 blank it says "Date" if not check error trap in Col Y.

VBA Noob

[Suamere]

Okay, Last question and I can pretty much give you the answer. What is the end result?

If the person puts in a bad address, do you want a message box to pop up and say "That address is bad" And delete the bad address?

What does D1 say if Y is not blank?

What does the Y say both when a good address is put in and a bad address is put in?

I am pretty much done with the "answer" but I don't want to post it until I am positive I have what you want.

[VBA Noob]

What is the end result?


If the person puts in a bad address, do you want a message box to pop up and say "That address is bad" And delete the bad address?
Pop up saying "Invalid date or Month - Please re enter" and delete the entry

What does D1 say if Y is not blank? Just the Word "Date".

What does the Y say both when a good address is put in and a bad address is put in? Currently a good address appears as blank.
A error appears if sheet Tab Year number 06 is different to that entered in D2 etc.
A different error message appears if a wrong format use such as 3009\06. The user gets <<< Error, Check date format



VBA Noob