Preserving date format in VB

[Guest]

I have some code which checks the entry in a cell is a date and prompts the
user for a date if it's not:

Dim ADate
Dim Message, Title, Default
Message = "Enter the date as d/m/yy"
Title = "Date Format"
Default = "25/12/07"
Worksheets("Summary").Range("E4").Activate
ActiveCell.NumberFormat = "d/m/yy"
ADate = ActiveCell
Do Until IsDate(ADate)
' Display message, title, and default value.
ADate = InputBox(Message, Title, Default)
ActiveCell.Value = ADate
Loop
ActiveCell.NumberFormat = "d mmmm yyyy"

The trouble is, if as the user, I input 1/8/07 (in place of "1st Aug 07"),
it returns the US format. ie 8 January 2007.
Can anyone help to correct this, please?

Apologies if you recognise any of the code - it is plagiarised from this site.
Thanks

 

[Bob Phillips]

Try this

Dim ADate
Dim Message, Title, Default
Message = "Enter the date as d/m/yy"
Title = "Date Format"
Default = "25/12/07"
Worksheets("Summary").Range("E4").Activate
ActiveCell.NumberFormat = "d/m/yy"
ADate = ActiveCell
Do Until IsDate(ADate)
' Display message, title, and default value.
ADate = InputBox(Message, Title, Default)
ActiveCell.Value = CDate(ADate)
Loop
ActiveCell.NumberFormat = "d mmmm yyyy"


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Jimbob,

try replacing this line:

ActiveCell.Value = ADate

with this:

ActiveCell.Value = CDate(Format(ADate, "dd/mm/yyyy"))

 

[Guest]

Bob, What a star you are!
Fabulous - works a treat.

Thank you so much