Full Version: Input Box in UK Date format
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
bdj
Can anyone please explain how I get the Input Box to take a UK formatted date. The following coding works perfectly except that it will insist on the input box in US format. Cell E2 is correctly formatted as UK

Dim Startdate as Date

Worksheets(1).Range("E2").Select
Selection.NumberFormat = "dd/mm/yyyy"
ActiveCell.FormulaR1C1 = InputBox("Input the first day of the new month ie 01/09/2002")
Startdate = ActiveCell.FormulaR1C1

An urgent reply would be appreciated as it’s holding me up.

Thanks
Scomac
Have u made sure that your region settings on your clock setting in control panel is set to UK standard date?
bdj
Yes the control settings are set for UK and UK date format. That was the only thing I could think of myself.
BradKennedy
bdj

You could always set the cell = to a variable that you format with code before entering into the cell.

Example
x="15-05-02"
Range("A1")= format(x, "dd-mm-yy")

HTH

Brad
bdj
unfortunately this would defeat the fully automated setting up of the spreadsheet.

Everything is based on the initial date being input. That is why I was using an input box in a macro rather than an input to a particular cell.

Thanks for all your help though

Brian
Scomac
mm i dunno but why not replace the input box with a userform?
BradKennedy
bdj,

What I showed you was code for an input box.
It takes the value and formats it, then outputs it.

If you need further explanation let me know.

Brad.
bdj
Believe it or not I resolved it by just reformatting the input to:-

Selection.NumberFormat = "dd/mmm/yyyy"

In this format there is no possibility of it using US format.

Thanks for all your input, I'm very grateful

Brian
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.