Excel - Restrict entry in specific cols - Dates

you could use the prev range from your other thread to validate the date at the same time, when you leave the cell
try something like
if prev.column = datecol then
if prev.value > "1/12/2007" or prev.value < "1/1/1970"

just the column number you want to check the date

try
if prev.numberformat = ' will have to match your set date format

on testing i found 2 problems,
1. prev.Value = UCase(prev.Value), the date i had in a cell 01/12/2007 (formatted dd/mm/yyyy) changed on the above line to 12/01/2007, probably the safest solution is to only ucase as an else to if the number format matches

2 your or should probably be and
edit: ignore this second one, i think it is incorrect

i think the code is correct, you need to fix the numberformat of your date column, don't know how you can get that format
easiest way is to set as custom format then put the dd/mm/yyyy, as that format was not available for me to select in the dates, even though it is my system locale date format

you need to first decide what date format are you going to use... dd/mm/yyyy or d/m/yyyy
I did set the format for the reqd cols as "dd/mm/yyyy" using the format->cells..
in break mode the number format shows (tooltip) "dd\/mm\/yyyy" ? and skips the code inside the if..else what is happening
koolsid your code is for checking each value being entered. The problem here is based on the format of a col.
We need to first check if the col is date col (formatted for date)
Then we need to check if the value is/or not equal to 01/01/1900
If not then check if the value is between 01/01/1970 and 30/11/2007
If both the check fails the the value is illegal, inform the user (say msgbox) and bring the focus back to the same cell and set the default date as 01/01/1900.
It should however allow the cell being left blank.
I did see your PM. but the problem is we are checking against more than 1 values ranges. 01/01/1900, between 01/01/1970 and 30/11/2007. Thus apparently this checking is not possible to be accomplished :afrog:

i do not understand why your numberformat is not returned as dd/mm/yyyy me too :D
how exactly are you setting you date format?Format>Cells..>Category(custom)>Type(dd/mm/yyyy).
I message boxed it (in sheet change event) still show dd\/mm\/yyyy:eek:

bear with me where do I specify the range that is in which event of the sheet

well if that works for you, use that

I dont suppose you want me to blindly use it?

looks to me as if it is you format cells option that is putting in the incorrect format, i think it must be putting in the backslash charater to escape the slash, which it must require as part of the language for the locale, that is the difference between numberformat and numberformatlocal

an example is :- similar to putting quotes in strings
as my computer only has english language installed it is hard for me to be more certain about what is happening