How to set date format when manually inserting date on excel, without changing regional settings?

In my locale, date is inserted as dd/mm/yyyy or variations of that (including dd-mm-yy). I made an Excel form (in normal Excel file), already formatted to show date as dd/mm/yyyy, but when I typed "05/04/2015" it will be shown as "04/05/2015". I know this can be solved by changing the regional setting to my local setting, but I need to distribute this form to a lot of people with no knowledge of changing regional setting, and assuming no one can teach how to, hence my question :

Is there any way to input "05/04/2015" and be recognized as "5 April 2015", without having to change regional settings ?

Additional note: When I mean "4 May 15" (typed in 4/5/15), Excel will recognize this as "5 April 15" because Excel takes input as "mm/dd/yy" format. I need to change the default behavior of Excel to take as "dd/mm/yyyy" as this is the way the users will input.

If possible, the setting should also included/embedded on the file to allow end-users use the form without even changing setting.

I'm not familiar with macros and VBA, but willing to try those :)

EDIT : this is related to my question : How to change date format from dd-mm-yyyy to dd-mm-yy in formula bar of MS-EXCEL?

but I don't need to change the whole formula bar, as long as Excel recognize that I'm typing in dd/mm/yy format.

Answer

The Worksheet_Change event could essentially accomplish what you're asking for, but you need to be sure the user intends things to change. I've added a 'Yes/No' choice box to my solution to prevent any accidental changes. Doing this without a choice box is risky because if you tried to edit an existing value the it would change back to the original formatting.

This code should 'live' inside the VBA code for the sheet you're wanting it to act on:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iDay, iMonth, iYear As Integer
Dim solutionRange As Range

'the cells you want the date to change for
Set solutionRange = Range("D4:D12")

'only triggers if the user's changing one cell
If Target.Count = 1 Then
    If (Not (Intersect(solutionRange, Target) Is Nothing) And (Target.Value > 0)) Then
        'disable application events so this change doesn't trigger itself
        Application.EnableEvents = False

        iDay = Left(Target.Value, 2)
        iMonth = Mid(Target.Value, 4, 2)
        iYear = Right(Target.Value, 4)

        ' Give the user a choice to say whether they need the date changed. You *can* remove both the 'If' and 'End If' if you really need to, but I'd strongly suggest keeping them.
        If (MsgBox("Have you entered your date in the format 'MM/DD/YYYY'?", vbYesNo) = vbYes) Then
            Target.Value = DateValue(iMonth & "/" & iDay & "/" & iYear)
        End If

        Application.EnableEvents = True
    End If
End If
End Sub