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]

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

I tried this too and didn't work either...

 

[RoryA]

Can you remove that code temporarily (cut and paste it into Notepad so you can put it back later) and just confirm that the problem goes away when you do that?

 

[Ingrid_SM]

I removed the code and it keeps doing the same.

 

[RoryA]

OK, then that isn't the code that is causing the problem, which explains why the fixes aren't working!

In the VB Editor, there is a window called the Project Explorer (usually top left) which will show all the open workbooks and all the components within each one. Locate your workbook, and the ThisWorkbook object in that window, and double-click it. Is there any code there? (particularly interested in a Workbook_SheetChange routine).

 

[Ingrid_SM]

I added the code again (from the Notepad).

Double clicking in ThisWorkbook there is nothing:

I only see Worksheet_Change when I double click the module with this code:

 

[RoryA]

That won't run automatically from there. What is the code in the Hoja31 class?

 

[Ingrid_SM]

That won't run automatically from there. What is the code in the Hoja31 class?

There is no code there. I don't even know why this is there. I guess I can delete it. (I didn't make this Workbook. I'm trying to help a colleague that is more beginner than me, and he doesn't know why that Hoja31 class is there).

 

[Joe4]

If this is the only sheet having this issue, maybe the best bet would be to recreate this sheet (and delete the old one).

 

[RoryA]

You could also either post a screenshot of the code module for the sheet in question, or remove any data and put the workbook itself on a sharing site like OneDrive or Dropbox for us to have a look at.

 

[Ingrid_SM]

If this is the only sheet having this issue, maybe the best bet would be to recreate this sheet (and delete the old one).

Yes, I can do that. Thanks!