I have had a maddenning time getting this to work and so I post this in the hope that it may help someone else with the same frustration (or just make the experts laugh because it looks so easy).

What I wanted was to have a date input by the user during the running of an Excel Macro.

My first attempt was:

DespatchDate = Application.InputBox( _
Prompt:="Expected Despatch Date", _
Default:=Format(Date, "dd/mm/yyyy"))
' Now store whatever is entered
Range("F1").Select
Selection.NumberFormat = "d/m/yyyy"
ActiveCell.FormulaR1C1 = DespatchDate

All worked fine and the InputBox even defaulted to today's date BUT when I tried it with 01/02/2009 (which should be 1st February) it went wrong because the result which appeared in cell F1 was 02/01/2009 (ie 2nd January). After a lot of head-banging I found a very simple solution which I share below:

RepeatGetDate:
DespatchDate = Application.InputBox( _
Prompt:="Expected Despatch Date", _
Default:=Format(Date, "dd/mm/yyyy"), _
Type:=1)
On Error GoTo RepeatGetDate
If DespatchDate = False Then
' This catches if the user enters something invalid like text
GoTo RepeatGetDate
End If
' Now store whatever is entered
Range("F1").Select
Selection.NumberFormat = "d/m/yyyy"
ActiveCell.FormulaR1C1 = DespatchDate

The error trapping helped, but it is the simple statement that Type:=1 that makes the real difference. As I understand it this makes the entry numerical and then Excel handles it correctly - simple or what!

I hope this helps someone else, because I was going to post my marbles here and ask for some new ones - heigh-ho!

Many thanks,
Caspar