Date Format

[Paul Bryan]

Using Excel 2002

Have written some simple code using the

Sub Auto_Open()
Trap_Entry_Log
End Sub

Sub Trap_Entry_Log()
ActiveWorkbook.Worksheets("Log").OnEntry = "Validate"
End Sub

Then use the code 'If .Column = #' , to control entry into each column

One column contains a Custom date format dd/mm/yy set to the United Kingdom
but when entered the displayed date changes to the American format
i.e. when 6/7/90 is entered it changes to 07/06/1990.

I have tried all sorts of date formats but no matter what i do i can't get
the correct format displayed.

Any ideas?

 

[Dave Peterson]

First, even though the .OnEntry procedures are still supported, you may want to
consider using worksheet events. You may find that there are lots more options
to control what you want to do.

This change won't help the date problem.

I'm not sure how you're getting the date to be entered though.

If your code creates it:

with receivingCell
.numberformat = "dd/mm/yyyy"
'don't use a string
.value = dateserial(yearVar, monthVar, dayVar)
'or
.value = date 'today
end with

If you're picking up the date from a sending cell

with receivingCell
.numberformat = "dd/mm/yyyy"
.value = sendingCell.value2
end with

The .value2 is useful when working with dates and currency.

 

[Paul Bryan]

The code searches a previous entries, if a condition is met the date is
copied from a previous cell and pasted into the ActiveCell, if the condition
is not met, the date (Date of Birth) is manually typed in.

Paul

 

[Dave Peterson]

Try it by assigning the value (actually .value2) instead of the copy|paste.

If the date is typed in, then the user's date setting wins. No matter how you
have the cell formatted, if the user types in a short date (like m/d/yy), then
excel will use the windows regional settings to parse the entry.

Just to add...

If you have to copy the cell to include the format, you could use two steps:

previouscell.copy _
destination:=activecell

activecell.value = previouscell.value2