Input Box Changing Date Format

[stucamps]

I have searched on the forums and tried different solutions to this common problem but to no avail.

Basically I have an input box where the user enters a date in the format "dd/mm/yyyy" and this is being returned as "mm/dd/yyyy"

I have checked my regional settings via the Control Panel and have reformatted the excel cell to "dd/mm/yyyy" both in VBA and also manually in Excel - Format>Cells>etc.

The Code I have for the InputBox is below and the formatting also
(I have included two versions of the InputBox but both still throw up the wrong date format)


Month = ThisWorkbook.Sheets(1).Range("$G$7").Value
Dim d
d = Format(InputBox("Please Enter the Inception Date for the new Share Class series you have added", "Date Entry", Format(Month, "dd/mm/yy")), "dd/mm/yyyy")
ActiveSheet.Range("A65536").End(xlUp).Offset(-14, 0).Value = Format(d, "dd/mm/yyyy")

or alternatively used this InputBox suggestion:

d = CDate(InputBox("Please Enter the Inception Date for the new Share Class series you have added", "Date Entry", Format(Month, "dd/mm/yy")))

I am trying to enter the date 1st May 2005 - as 01/05/2005 - but it is being returned as 05/01/2005.

Can someone please point out something I am missing here!

Thanks,
Stuart

 

[whiteghost]

have you tried formating the cell on the sheet where the date from the inputbox? I had the same problem with textboxes in a userform.
the date has to entered as mm/dd/yy, but the cell it's going to can have dd/mm/yy format

 

[stucamps]

Yep, I had tried that as well and it still keeps the value as before so through VBA I create the date 01/05/2005 (1st May 2005) - VBA converts this to 05/01/2005 (5th January 2005) so when I format it "manually" through Excel it still thinks that the correct date I want is 5th Jan. - I can manually take it back to 1st May but this isnt what I want to do as VBA is to run this automatically.
Thanks
Stuart

 

[Andrew Poulsom]

VBA is notoriously flaky with UK dates. This worked for me:

Sub Test()
    Dim Month
    Dim d
    Month = ThisWorkbook.Sheets(1).Range("$G$7").Value
    d = Format(InputBox("Please Enter the Inception Date for the new Share Class series you have added", "Date Entry", Format(Month, "dd/mm/yy")), "dd/mm/yyyy")
    With ActiveSheet.Range("A65536").End(xlUp).Offset(-14, 0)
        .Value = CLng(DateValue(d))
        .NumberFormat = "dd/mm/yyyy"
    End With
End Sub

 

[stucamps]

Thanks alot Andrew - again you have solved my problem. You my friend are a true gent and a scholar
Stuart