Why is Excel changing the date I enter to American style?

[Ingrid_SM]

Hi there,

I have a workbook with a field to enter a date in format dd-mmm-yyyy.
If the day is lower than 13, it takes the day as the month and the month as the day and gives me the date in the same format dd-mmm-yyyy but with the date that would result in American style.
For example, I want to enter 11 (day) / 05 (month = May) / 2021. When I do enter, I get 05 (day) - November - 2021.
If I enter the date when the day is higher than 12, it does it correctly. For example: I enter 13 (day) / 05 (month = May) / 2021. It gives me 13-may-2021.
In cell format, I have: Regional configuration = Spain.

View attachment 43217

In Control Panel / Region, I have Format = Spanish too...

View attachment 43218

What's wrong?

Thanks in advance!

 

[Ingrid_SM]

RoryA,

Yes, there is this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Target.Value = VBA.UCase(Target.Value)
Application.EnableEvents = True
On Error GoTo 0

End Sub

 

[RoryA]

There's the problem (seems weird to force upper case on an entire sheet to me?). Change this line:

Target.Value = VBA.UCase(Target.Value)

to:

If not isdate(target.value) then Target.Value = VBA.UCase(Target.Value)

 

[Ingrid_SM]

I did, but it does the same...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
'Target.Value = VBA.UCase(Target.Value)
If Not IsDate(Target.Value) Then Target.Value = VBA.UCase(Target.Value)
Application.EnableEvents = True
On Error GoTo 0

End Sub

 

[RoryA]

I can't replicate that. I set my control panel to Spanish settings and the code works fine with the IsDate addition (without it, I get the problem you described originally)

 

[Ingrid_SM]

I wrote down the date and, before hiting enter, I videotaped it in slow motion: it turns out that excel writes the date correctly and then it changes it! So weird...

Thanks everyone for trying! I really appreciate it.

Íngrid

 

[RoryA]

Try this version instead:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
'Target.Value = VBA.UCase(Target.Value)
If Not IsNumeric(Target.Value2) Then Target.Value = VBA.UCase(Target.Value)
Application.EnableEvents = True
On Error GoTo 0

End Sub

 

[Ingrid_SM]

Still the same...

It's ok, don't worry, I can work around it. I just really wanted to know what was wrong... but sometimes it is just not possible...
Thanks for all your time!

Íngrid

 

[Alex Blakenburg]

Don't give up yet.
I was able to replicate the issue and both (or is it either) of Rory's options will fix the problem.

If you need to trouble shoot it further though I prefer the longer format of the if statement so you can set a break point on the "If" line and whether or not it performs the Ucase line.

Did you copy Rory's code and fully replace the code that was there ?

If Not IsNumeric(Target.Value2) Then
    Target.Value = VBA.UCase(Target.Value)
End If

 

[Alex Blakenburg]

Actually leveraging of Rory's last code change

Just changing this to Value2 fixes it:-
VBA.UCase(Target.Value)
to
VBA.UCase(Target.Value2)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
    On Error Resume Next
    Application.EnableEvents = False
    Target.Value = VBA.UCase(Target.Value2)      ' Changed to Value2
    Application.EnableEvents = True
    On Error GoTo 0

End Sub

 

[Ingrid_SM]

Don't give up yet.
I was able to replicate the issue and both (or is it either) of Rory's options will fix the problem.

If you need to trouble shoot it further though I prefer the longer format of the if statement so you can set a break point on the "If" line and whether or not it performs the Ucase line.

Did you copy Rory's code and fully replace the code that was there ?

If Not IsNumeric(Target.Value2) Then
    Target.Value = VBA.UCase(Target.Value)
End If

I tried this and didn't work...