Date formatting form with VBA to excel worksheet

[ken donovan]

Hi,

I have built a VBA form with a date input field. When I save the form the date formatting changes from the date that I entered 01-06-2018 to 06-01-2018 the date that appears in the excel cell. 

I have checked the region settings and the date & time formats setting on my computer and they are correct dd/MM/yyyy. My VBA code is

'Format cells as a date
sh.Range("C6", "C2000").NumberFormat = "dd-MM-yyyy"

but it keeps thinking I am inputting the date in US format and changes it to European format.

Can anyone help please.

[#2]

Hi Ken,

1. Please match your Region settings exactly as dd-MM-yyyy and not as dd/MM/yyyy.

2. You don't need this statement sh.Range("C6", "C2000").NumberFormat = "dd-MM-yyyy"

3. While saving your date, you can use Cells(Row, Column).Value = DateValue("01-06-2018") or if you use Range then you can use Range(CellAddress).Value instead of Cells(Row,Column).Value.

Hope this helps. This works on my Comp. Windows 10, and Office 365.

Regards,

San.