Advice for correct date to be shown on worksheet & in userform

[ipbr21054]

Hi,
On my worksheet i type in a cell 01/04/2023 BUT when i leave the cell i see it change to 04/01/2023

I open my userform & for the date issue above i see the dat as 1/4/2023 in the Listbox
Onb the worksheet the date is in column E4 then down the page

Right clicking the worksheet cell & selecting format its shown as in screenshot

Please see screenshot for the date issue that i mention.

My goal is to code so when i type 01/04/2023 the worksheet DOES NOT change ^ also the uderform also shows 01/04/2023

Many Thanks

 

[NoSparks]

So i click on 04/01/2023 then do the immediate window & i see 44930

That's January 4th alright so there is no point in working with the formatting until Excel accepts the date you actually want.
Go back to that 04/01/2023 cell and delete it, set the cell formatting to General and try typing in 01/04/2023 again.
Your regional setting should accept at first of April 2023 and that .value2 = 45017
If it isn't then you got something else going on that needs fixed

 

[ipbr21054]

Changes to 04/01/2023 again

 

[ipbr21054]

See below
I deleted it & typed 01/04/2023 & no changed happened.
I put the code back & typed 01/04/2023 & watched it change to 04/01/2023

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
        End If
    End With
    
End Sub

 

[ipbr21054]

Ive deleted that code so will leave it like so as it work.

Thanks

 

[NoSparks]

I think that's a good idea... don't know why you would upper case a column of dates like that anyway.