Entering Dates and auto-completing

  • Thread starter Thread starter junior
  • Start date Start date
J

junior

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
 
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
 
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!
 
It shouldn't be a problem. I can't say why it doesn't work in your existing
workbook.
 
OK, I'll try a few experiments to see if I can find the problem - I'll
let you know.

Thanks again.
Dick
 
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
 
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
 
Yes, exclusively within the range 1-31, in fact almost always using
just single digit for testing. (Actually, using 0 and 32 resolve
correctly to 31Jan04 and 03Mar04 respectively.)

Just tried the "is not numeric" version and this generates the error
msg on every 4th entry, regardless of the digit(s) used.

1 > 01Feb04
2 > 02Feb04
3 > 03Feb04
4 > 04Jan00 - and the error box: "3/01/1900 is not numeric"

another series:

28 > 28Feb04
29 > 29Feb04
27 > 27Feb04
26 > 26Jan00 - and the error box "25/01/1900 is not numeric"

The above series both were entered in A1 to A4. If I skip a row
between each of those entries, ie A1, A3, A5, A6, I can go through the
whole series (1-29) without error.

So it seems that if a series is entered in adjacent cells, every 4th
entry will generate an error. If the cells are not adjacent, no error
is generated. Very strange!

I'll keep prodding!

Using Excel 2002 by the way.


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
Click to expand...

(snipped)
 
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.
 
G'day Tom

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

Regards
Dick
 
Back
Top