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

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

I know exactly what you are talking about. I like to type YY-MM-DD. The setting to change it to what you want is not in Excel. The setting you want is in windows. It is hidden somewhere in the control panel "time and language".

I had to go to an advanced setting "Change Time and Date Format" and change the "Short Date" setting.

Depending on which operating system you use the directions could be a little different.

Hope this helps.

I do not think you can change the default. It's especially awkward to fix when you import data from overseas.

The least messy ways of doing this I can think of is some sort of manual way...

Option 1: We could accept the inputs in text format and put them in the cells, this way Excel won't try to interpret them as dates. Then, in an adjacent cell we could use the function DATE together with LEFT, RIGHT, and MID. For example (specify that the user must enter the date in full MM/DD/YYYY):

Cell A1: 04/05/2015

Cell B1: =DATE( RIGHT(A1,4), LEFT(A1,2), MID(A1,4,2) )

Option 2: Request that the user enters the 'day', 'month' and 'year' into three separate cells or fields, which might be better or worse depending on your set up.

Anyhow, good luck!