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,

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,

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,

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,