Checking for valid dates on entry
 

I have a sheet where users enter dates. Other routines run and
highlight cells in different colours depending on the dates.

If a user enters a date with a leading space, it still appears in the
cell to be a date but VBA thinks it is not. It is acceptable to have
a blank cell.

I am trying to trap these errors with the change event, but cannot
get it to work.

Any ideas.



validdate = Now() + 730

If Len(Trim(cell.Value)) 0 And (cell.Value validdate) Then MsgBox
"invalid date"



Thanks,

Ian,

Checking for valid dates on entry
 

1. trap the change with the change event
2. determine if it is a cell of concern
3. Use the IsDate() function to test for valid
--
Gary''s Student


"Ian" wrote:

I have a sheet where users enter dates. Other routines run and
highlight cells in different colours depending on the dates.

If a user enters a date with a leading space, it still appears in the
cell to be a date but VBA thinks it is not. It is acceptable to have
a blank cell.

I am trying to trap these errors with the change event, but cannot
get it to work.

Any ideas.



validdate = Now() + 730

If Len(Trim(cell.Value)) 0 And (cell.Value validdate) Then MsgBox
"invalid date"



Thanks,

Ian,

Checking for valid dates on entry
 

Check out Data, Validation where you specify date range.

"Ian" wrote in message
...
I have a sheet where users enter dates. Other routines run and
highlight cells in different colours depending on the dates.

If a user enters a date with a leading space, it still appears in the
cell to be a date but VBA thinks it is not. It is acceptable to have
a blank cell.

I am trying to trap these errors with the change event, but cannot
get it to work.

Any ideas.



validdate = Now() + 730

If Len(Trim(cell.Value)) 0 And (cell.Value validdate) Then MsgBox
"invalid date"



Thanks,

Ian,

Checking for valid dates on entry
 

If you must use VBA (as opposed to data validation in XL), check the
IsDate VBA function.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have a sheet where users enter dates. Other routines run and
highlight cells in different colours depending on the dates.

If a user enters a date with a leading space, it still appears in the
cell to be a date but VBA thinks it is not. It is acceptable to have
a blank cell.

I am trying to trap these errors with the change event, but cannot
get it to work.

Any ideas.



validdate = Now() + 730

If Len(Trim(cell.Value)) 0 And (cell.Value validdate) Then MsgBox
"invalid date"



Thanks,

Ian,