Entering Dates and auto-completing
 

I enter a lot of dates (ddmmmyy) in column A on a month-by-month
basis. Is there a way to just enter the "dd" and have the entry
automatically completed to include the "mmmyy" ? ie, I enter "27" and
when I hit 'enter' the cell is automatically completed to "27Feb04".

It is very easy to do using seperate columns/cells of course, ie in A1
enter "27" and have B1 =A1+38017 (with column B previously formatted
to "ddmmmyy"), but is there a way to accomplish this directly within
the same cell, A1?

Thanks

Entering Dates and auto-completing
 

Right click on the sheet tab where you want this behavior and select view
code.

Paste in code like this in the resulting module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim dt As Date
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Column = 1 Then
If IsNumeric(Target.Value) Then
dt = DateSerial(Year(Date), Month(Date), Target.Value)
Application.EnableEvents = False
Target.Value = dt
Target.NumberFormat = "ddmmmyy"
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy



wrote in message
...
I enter a lot of dates (ddmmmyy) in column A on a month-by-month
basis. Is there a way to just enter the "dd" and have the entry
automatically completed to include the "mmmyy" ? ie, I enter "27" and
when I hit 'enter' the cell is automatically completed to "27Feb04".

It is very easy to do using seperate columns/cells of course, ie in A1
enter "27" and have B1 =A1+38017 (with column B previously formatted
to "ddmmmyy"), but is there a way to accomplish this directly within
the same cell, A1?

Thanks

Entering Dates and auto-completing
 

Thanks Tom, that works like magic and it will save me a lot of time -
and mistakes!

I tried it on a new sheet and it works just fine, but when I applied
it to an existing sheet the "year" reverted back to "00". Is there a
way to add this to a pre-existing sheet?

Thanks again, most helpful!


On Mon, 23 Feb 2004 19:34:19 -0500, "Tom Ogilvy"
wrote:

Right click on the sheet tab where you want this behavior and select view
code.

Paste in code like this in the resulting module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim dt As Date
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Column = 1 Then
If IsNumeric(Target.Value) Then
dt = DateSerial(Year(Date), Month(Date), Target.Value)
Application.EnableEvents = False
Target.Value = dt
Target.NumberFormat = "ddmmmyy"
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Entering Dates and auto-completing
 

It shouldn't be a problem. I can't say why it doesn't work in your existing
workbook.

--
Regards,
Tom Ogilvy

wrote in message
...
Thanks Tom, that works like magic and it will save me a lot of time -
and mistakes!

I tried it on a new sheet and it works just fine, but when I applied
it to an existing sheet the "year" reverted back to "00". Is there a
way to add this to a pre-existing sheet?

Thanks again, most helpful!


On Mon, 23 Feb 2004 19:34:19 -0500, "Tom Ogilvy"
wrote:

Right click on the sheet tab where you want this behavior and select view
code.

Paste in code like this in the resulting module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim dt As Date
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Column = 1 Then
If IsNumeric(Target.Value) Then
dt = DateSerial(Year(Date), Month(Date), Target.Value)
Application.EnableEvents = False
Target.Value = dt
Target.NumberFormat = "ddmmmyy"
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Entering Dates and auto-completing
 

OK, I'll try a few experiments to see if I can find the problem - I'll
let you know.

Thanks again.
Dick

On Mon, 23 Feb 2004 23:20:50 -0500, "Tom Ogilvy"
wrote:

It shouldn't be a problem. I can't say why it doesn't work in your existing
workbook.

Entering Dates and auto-completing
 

Something very strange is happening here!

Forget what I said earlier about the year reverting to "00" if the
column was pre-formatted to yymmmdd.

What is happening is that the code seems to run for only the first 3
cells ie A1, A2, A3. When we get to A4, the month/year are reset to
Jan/00. The Jan/00 will then continue to be returned from there on
down.

However, if I then select a cell at least 4-5 cells farther down the
column, let's say A10, A11, A12 will all return correct dates - but
A13 will again revert to Jan/00. Very strange!

Now delete that column A. Select new cell A30, enter a digit and then
use up-arrow to go to the cell above. Keep entering a digit and
moving to the cell above using the up-arrow. It all works perfectly
with no resetting to Jan/00.

As I said, something very strange is happening here! Your insight is
much appreciated.

Regards
Dick


On Tue, 24 Feb 2004 17:30:14 +1000, wrote:

OK, I'll try a few experiments to see if I can find the problem - I'll
let you know.

Thanks again.
Dick

On Mon, 23 Feb 2004 23:20:50 -0500, "Tom Ogilvy"
wrote:

It shouldn't be a problem. I can't say why it doesn't work in your existing
workbook.

Entering Dates and auto-completing
 

Are you entering numbers between 1 and 31 inclusive?

Perhaps the strange behavior is because you are entering something that
doesn't resolve to numeric. The code skips if the entry doesn't pass the
isnumeric test.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim dt As Date
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Column = 1 Then
If IsNumeric(Target.Value) Then
dt = DateSerial(Year(Date), Month(Date), Target.Value)
Application.EnableEvents = False
Target.Value = dt
Target.NumberFormat = "ddmmmyy"
Else
msgbox Target.Value & " is not numeric"
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

You can also turn off the errhandler and see if an entry is causing an error

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim dt As Date
If Target.Count 1 Then Exit Sub
'On Error GoTo ErrHandler
If Target.Column = 1 Then
If IsNumeric(Target.Value) Then
dt = DateSerial(Year(Date), Month(Date), Target.Value)
Application.EnableEvents = False
Target.Value = dt
Target.NumberFormat = "ddmmmyy"
Else
msgbox Target.Value & " is not numeric"
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

But if you do get an error, then you will have to specifically reenable
events

Sub Re_enable()
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

wrote in message
...
Something very strange is happening here!

Forget what I said earlier about the year reverting to "00" if the
column was pre-formatted to yymmmdd.

What is happening is that the code seems to run for only the first 3
cells ie A1, A2, A3. When we get to A4, the month/year are reset to
Jan/00. The Jan/00 will then continue to be returned from there on
down.

However, if I then select a cell at least 4-5 cells farther down the
column, let's say A10, A11, A12 will all return correct dates - but
A13 will again revert to Jan/00. Very strange!

Now delete that column A. Select new cell A30, enter a digit and then
use up-arrow to go to the cell above. Keep entering a digit and
moving to the cell above using the up-arrow. It all works perfectly
with no resetting to Jan/00.

As I said, something very strange is happening here! Your insight is
much appreciated.

Regards
Dick


On Tue, 24 Feb 2004 17:30:14 +1000, wrote:

OK, I'll try a few experiments to see if I can find the problem - I'll
let you know.

Thanks again.
Dick

On Mon, 23 Feb 2004 23:20:50 -0500, "Tom Ogilvy"
wrote:

It shouldn't be a problem. I can't say why it doesn't work in your
existing
workbook.

Entering Dates and auto-completing
 

G'day Tom

That's done the trick - thanks very much for all your assistance.
Much appreciated!

Regards
Dick

On Tue, 24 Feb 2004 22:15:36 -0500, "Tom Ogilvy"
wrote:

It appears that every 4th cell is formatted as date. So change the code to
this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim dt As Date
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Column = 1 Then
If IsNumeric(Target.Value2) Then
dt = DateSerial(Year(Date), Month(Date), Target.Value2)
Application.EnableEvents = False
Target.Value = dt
Target.NumberFormat = "ddmmmyy"
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

use the Value2 property for the target rather than value. This should clear
it up.