UserForm adds today's date to a text box and writes it to a worksheet, but the date changes to US format whenever the "day" is 12 or less (IE a month)

[tsgnms]

My workbook is set up to default to UK date format, and I'm specifying the date format I want in my code. However, if the day is 12 or less then Excel interprets it incorrectly and saves it the wrong way round. For example, if I try to write "05/10/2023" to a cell, it saves it as "10/05/2023". However, if I were to write "13/10/2023", it writes it correctly. I am extremely confused as to why it keeps switching the day and month around when none of my settings are set to anything but UK format. Furthermore, I have specifically stated in my code that I want the day to be first.
So, firstly I have a user form which saves to a hidden sheet (and can be recalled). There is a textbox in the form for a "last updated" value, so anytime someone saves a record it automatically adds today's date into the text box and also saves it to the table on the sheet:

Me.Controls("iLastUpdate").Value = Format(Now(), "dd/mm/yyyy")

I have tried this with both capitals and lower case lettering, and the text added to the field in the user form displays correctly. IE, if I run that code now, the text box in the user form shows "10/05/2023".
But then when I try to recall the data to the form, or view it in the table, it shows as "05/10/2023". This only happens when the first two numbers are 12 or less and can be considered a valid month.

The code I'm using to write to the table is as follows:

For Each cell In Table.Range("A2:AA2")
                If cell.Value <> "" Then
                    Table.Cells(trg.Row, cell.Column).Value = UCase(Me.Controls(cell.Value).Value)
                End If
            Next cell

I hope I've explained that okay, and any help would be seriously appreciated as it's incredibly frustrating!

 

[RoryA]

You need to use CDate to convert the text in the control to an actual date value using your regional settings - for example:

For Each cell In Table.Range("A2:AA2")
                If cell.Value <> "" Then
                    If Isdate(Me.Controls(cell.Value).Value) then
                       Table.Cells(trg.Row, cell.Column).Value = CDate(Me.Controls(cell.Value).Value)
                    else
                       Table.Cells(trg.Row, cell.Column).Value = UCase(Me.Controls(cell.Value).Value)
                    end if
                End If
            Next cell

 

[tsgnms]

Thank you so much, you are a hero!

I'd tried using CDate() but at the wrong part of the process - I was trying to convert it before storing it in the textbox which was, in hindsight, pretty stupid!

Thank you again!

 

[RoryA]

Glad we could help.