I re-tested using the ISDATE in xl2003 all updates without a glitch using
4/1. I also do the disable if testing shows a need.........On error was also
not needed. I have been doing this for more than a week...... However, I DO
sometimes make mistakes.....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Joe User" <joeu2004 wrote in message
...
"tompl" wrote:
I could not get that to work but this did:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsDate(Target) Or Target.Column < 1 Then Exit Sub
Target.Value = DateSerial(2009, Month(Target), Day(Target))
End Sub

Well, it might __appear__ to work. But it results in 226 calls for each
change to a date, at least in Excel 2003 / VBA 6.5.1024.

I think it is (usually) prudent to disable events in event macros.
Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsDate(Target) Or Target.Column < 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = DateSerial(2009, Month(Target), Day(Target))
Application.EnableEvents = True
End Sub

I usually use On Error to ensure that EnableEvents=True is executed even
if there is an error. But this macro seems straight-forward enough.


----- original message -----

"tompl" wrote in message
...
I could not get that to work but this did:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsDate(Target) Or Target.Column < 1 Then Exit Sub
Target.Value = DateSerial(2009, Month(Target), Day(Target))
End Sub

Tom

"Don Guillett" wrote:

Agree that you don't care about the year but to do as you ask for
numbers in
col A
right click sheet tabview codeinsert thisformat your column as
desired.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsNumeric(Target) Or Target.Column < 1 Then Exit Sub
Target.Value = DateSerial(2009, Month(Target), Day(Target))
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JCarlosJr" wrote in message
...
Hi gang,

Its tax time and I'm trying to enter my expenses ito EXCEL for last
year
(don't even remind me I should do this as the year progresses).

Anyway, for speed of entry I would like to enter just mm/dd. Excel
will
complete the year for me as 2010.

I could change the system date, but this has bad reprocussions,
especaill
when multi-tasking.

I could create a column which subtracts 365 from my entry column and
then
paste values from my "dummy" column into my entry column.

BUT, does anyone have a better suggestion to force dates entered on a
specific book, sheet, or even colum to s different default year than
the
system date year?


.