Excel 97 macro query

  • Thread starter Thread starter PaulS
  • Start date Start date
P

PaulS

Can anyone help (or point me in the right direction) with
a macro that will automatically change a year entered into
a cell into day/month/year format. For example in column
A"1991" entered into a cell would automatically change to
01/01/1991, in column B "1991" entered into a cell would
automatically change to 31/12/1991.

The reasoning behind this is the spreadsheet is filtered
at a later date and only a small proportion of the data
originally input is transferred to a mastersheet. To input
dates in the dd/mm/yyyy layout at each entry would waste a
lot of time and effort

Many thanks for any advice you can give
 
Paul,

This worksheet event code should do it

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1,B1")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Address = "$A$1" Then
Target.Value = DateSerial(Target.Value, 1, 1)
Else
Target.Value = DateSerial(Target.Value, 12, 31)
End If
End If
End If

ws_exit:

Application.EnableEvents = True
End Sub

This goes into the worksheet code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Why not first try to use menu Format, Cells, Custom and in
the "Type" space create the format if it is not already
there?
 
typo alert!

I think Bob wants that first:

Application.EnableEvents = True
to read
Application.EnableEvents = False

(He wants to turn it off so changing the cell doesn't fire the macro again.)
 
Back
Top