Text Box date format dd/mm/yy not mm/dd/yy

[BaggieDan]

Hi,

Quick question (I hope!!),

I have a very simple user form that features a combobox, two text boxes and
two command buttons.

The combobox drops down a list of employees, the two text boxes allow the
user to input a start date and end date and the the command boxes are an
enter and cancel button.

Its a very basic Holiday recorder hence the need for a Start and End date.

When the user selects the 'enter' button the following should happen. The
correct worksheet is activated, the start and end dates are entered in the
next blank row, the worksheet calculates the difference.

The problem I am having is that the user enters the date in the textbox as
follows :

01/02/09

which is the uk format dd/mm/yy.

However when the date appears in the employees spreadsheet it is changed to :

02/01/09

which is the american format of mm/dd/yy.

Which turns a one day holiday (01/01/09 to 02/01/09) into a 31 day holiday
(01/01/09 to 01/02/09) which is not good !!!

The following is the very basic code that runs when the enter button is
selected:

Worksheets("Front").Activate
Application.ScreenUpdating = False

If ComboBox1 = "John Smith" Then Worksheets("JS").Activate
If ComboBox1 = "Joe Bloggs" Then Worksheets("JB").Activate

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

Cells(NextRow, 1) = StartDate.Text
Cells(NextRow, 2) = EndDate.Text
Cells(NextRow, 3).Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Cells(NextRow, 4).Select
ActiveCell.Value = Date



StartDate.Text = ""
EndDate.Text = ""
Worksheets("Front").Activate
Application.ScreenUpdating = False

End Sub


I know its not very pretty and could probably be written much better - I am
completely self taught so go easy!!

I have tested it and it works fine apart from the date issue. How can I get
the date to appear in the correct dd/mm/yy format !?!?

The cells in the employees spreadsheet are formatted correctly (Date, 14th
March xxxx).

Thanks in advance

Dan

 

[Joel]

The code you provided isn't moving the data from the combobox to the
worksheet. Either you didn't provide that code or you are using a linked
cell to put the data in the worksheet. I think there is an option in your
workbook that is set wrong. Check Tools - Options

 

[Tim Zych]

Is the setting in the Regional options in the Control Panel correct? It
could be set to EN English (United States) for this user which would cause
the behavior described.

 

[BaggieDan]

Hi,

Thanks for your replies.

Joel - The data that appears in the combobox does not need to appear in the
worksheet, it is purely used to identify which worksheet needs to be
activated, hence the If combobox="xxxxx" Then worksheet"xx".activate lines.

Tim - The regional settings are set to English (United Kingdom), in both the
Control Panel and the Cell format in Excel.

Any other ideas??

Thanks

Is the setting in the Regional options in the Control Panel correct? It
could be set to EN English (United States) for this user which would cause
the behavior described.

--
Tim Zych
http://www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
Free and Pro versions available

Hi,

Quick question (I hope!!),

I have a very simple user form that features a combobox, two text boxes
and
two command buttons.

The combobox drops down a list of employees, the two text boxes allow the
user to input a start date and end date and the the command boxes are an
enter and cancel button.

Its a very basic Holiday recorder hence the need for a Start and End date.

When the user selects the 'enter' button the following should happen. The
correct worksheet is activated, the start and end dates are entered in the
next blank row, the worksheet calculates the difference.

The problem I am having is that the user enters the date in the textbox as
follows :

01/02/09

which is the uk format dd/mm/yy.

However when the date appears in the employees spreadsheet it is changed
to :

02/01/09

which is the american format of mm/dd/yy.

Which turns a one day holiday (01/01/09 to 02/01/09) into a 31 day holiday
(01/01/09 to 01/02/09) which is not good !!!

The following is the very basic code that runs when the enter button is
selected:

Worksheets("Front").Activate
Application.ScreenUpdating = False

If ComboBox1 = "John Smith" Then Worksheets("JS").Activate
If ComboBox1 = "Joe Bloggs" Then Worksheets("JB").Activate

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

Cells(NextRow, 1) = StartDate.Text
Cells(NextRow, 2) = EndDate.Text
Cells(NextRow, 3).Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Cells(NextRow, 4).Select
ActiveCell.Value = Date



StartDate.Text = ""
EndDate.Text = ""
Worksheets("Front").Activate
Application.ScreenUpdating = False

End Sub


I know its not very pretty and could probably be written much better - I
am
completely self taught so go easy!!

I have tested it and it works fine apart from the date issue. How can I
get
the date to appear in the correct dd/mm/yy format !?!?

The cells in the employees spreadsheet are formatted correctly (Date, 14th
March xxxx).

Thanks in advance

Dan