Input Data from user in correct Format

[Guest]

I would like to be able to prompt the user to enter a date and place this
information in a cell (with a number format which has category Custom and
Type "dd/mm/yyyy" .

I tried the following code, however if the user enters 7/2/05 it will appear
in the cell as 2/7/05:

Message = "Enter the start date for the report"
Title = "Start Date"
Default = "7/2/05"
StartDate = InputBox(Message, Title, Default)

Sheets("Weekly").Select
Range("G5").Select
ActiveCell.NumberFormat = "dd/mm/yyyy"
ActiveCell.FormulaR1C1 = StartDate

Any suggestions as to how to fix this would be much appreciated.

 

[Tom Ogilvy]

Dim dtStart as Date, Message as String, Title as String
Dim StartDate as String
Message = "Enter the start date for the report"
Title = "Start Date"
Default = "7/2/05"
StartDate = InputBox(Message, Title, Default)

If isdate(StartDate) then
dtStart = cDate(StartDate)
With Sheets("Weekly").Range("G5")
.NumberFormat = "dd/mm/yyyy"
.Value = dtStart
End With
Else
msgbox "This is not a valid date"
End if

 

[Guest]

Thanks Tom

That works exactly as required.

Dim dtStart as Date, Message as String, Title as String
Dim StartDate as String
Message = "Enter the start date for the report"
Title = "Start Date"
Default = "7/2/05"
StartDate = InputBox(Message, Title, Default)

If isdate(StartDate) then
dtStart = cDate(StartDate)
With Sheets("Weekly").Range("G5")
.NumberFormat = "dd/mm/yyyy"
.Value = dtStart
End With
Else
msgbox "This is not a valid date"
End if