Excel and regional settings

Question

Is there any way to disable excel to read regional settings for date cell? Currently present format in cell is different for display and edit, like it is present below:

Answer 1

Put the folling into a module: For this to work we need to store the users initial setting in a public variable. This will need to be put into a Module not an Object

Option Explicit
Public InternationalDateSetting As XlApplicationInternational

Then put this into the ThisWorkbook object. When the user opens the Workbook it will set the users setting to the public variable. Then in this when the user deactivates the workbook (i.e. changes to another workbook or another program) it will reset the users setting back to default and it will then change it to your custom setting when the user activates the workbook. In doing this it will control the Application settings but be workbook specific

Option Explicit
Private Sub Workbook_Open()
    InternationalDateSetting = Application.International(xlMDY)
End Sub
Private Sub Workbook_Activate()
    '' Please look at https://msdn.microsoft.com/en-us/library/office/ff840213.aspx for all settings
    '' Set to true for month-day-year order, false for day-month-year
    Application.International(xlMDY) = True
    '' Or uncomment and use:
    '' 0 = month-day-year
    '' 1 = day-month-year
    '' 2 = year-month-day
    '' You will also need to change the Workbook_Open and Workbook_Deactivate from 'xlMDY' to 'xlDateOrder'

    ' Application.International(xlDateOrder) = 0
End Sub
Private Sub Workbook_Deactivate()
    Application.International(xlMDY) = InternationalDateSetting
End Sub