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

 

[duggie33]

Sounds like a formatting issue. "mm/dd/yyyy" or "dd/mm/yyyy"

Hope that helps,

Doug

 

[ipbr21054]

I added this line in my worksheet change event.

Sheet12.Range("E4", "E200").NumberFormat = "mm-dd-yyyy"

If i used dd-mm then when i entered 01/04/2023 it changed it to 04/01/2023
So by using mm-dd it now works as in correct date but i dont think this is the correct way to go as im sure so far down the rd it might sort itself out then start playing games with the date agaion & all will be wrong

Need to find why its changing.

I click in the cell that shows 01/04/2023 & in the address bar i see 04/01/2023 ?????????

 

[duggie33]

I am not sure how it is all related, but it might have something to do with your Windows Date and Time settings.

 

[ipbr21054]

Ive checked all that and its fine,other sheets not affected

 

[ipbr21054]

I am using the code below.

My worksheet shows 01/04/2023 BUT the Listbox shows 1/04/2023
Can wee apply dd/mm/yyy to the sections shown id Red below, If so what is the correct way to do this.
Many thanks im hoping that would then fix it

Private Sub TextBox1_Change()
  TextBox1 = UCase(TextBox1)
  Dim r As Range, f As Range, cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("KDX2LIST")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 7
    .ColumnWidths = "160;230;90;110;180;100"

    If TextBox1.Value = "" Then Exit Sub
    Set r = Range("B4", Range("B" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlPart)
    If Not f Is Nothing Then
      cell = f.Address
      Do
        added = False
        For i = 0 To .ListCount - 1
          Select Case StrComp(.List(i), f.Value, vbTextCompare)
            Case 0, 1
              .AddItem f.Value, i
              .List(i, 6) = f.Row                'ROW NUMBER
              .List(i, 1) = f.Offset(, -1).Value 'CUSTOMER
              .List(i, 2) = f.Offset(, 2).Value  'YEAR
              .List(i, 3) = f.Offset(, 3).Value  'DATE
              .List(i, 4) = f.Offset(, 4).Value  'VIN
              .List(i, 5) = f.Offset(, 5).Value  'TOOL USED

              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
              .List(.ListCount - 1, 6) = f.Row                'ROW NUMBER
              .List(.ListCount - 1, 1) = f.Offset(, -1).Value 'CUSTOMER
              .List(.ListCount - 1, 2) = f.Offset(, 2).Value  'YEAR
              .List(.ListCount - 1, 3) = f.Offset(, 3).Value  'DATE
              .List(.ListCount - 1, 4) = f.Offset(, 4).Value  'VIN
              .List(.ListCount - 1, 5) = f.Offset(, 5).Value  'TOOL USED

        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> cell
      TextBox1Search = UCase(TextBox1Search)
      .TopIndex = 0
      Else
      MsgBox "NO INFO WAS FOUND", vbCritical, "KDX2 SEARCH VEHICLE"
      TextBox1.Value = ""
      TextBox1.SetFocus
    End If
  End With
End Sub

 

[NoSparks]

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

In words, are you trying to type in January fourth or April first ?

Ive checked all that and its fine

In Windows Settings > Time & Language > Region
What does it say for Long date

 

[ipbr21054]

Hi,
I am typing 1st April 2023 BUT it changes to 4th JANUARY 2023
So the code was added to correct it even though i think this isnt the correct way for fix it.

Sheet12.Range("E4", "E200").NumberFormat = "mm/dd/yyyy"

See attached
ddMMMM yyyy

 

[NoSparks]

I am typing 1st April 2023 BUT it changes to 4th JANUARY 2023

Lets check and see if the date changed or just the way it gets displayed
Select that 04/01/2023 cell on the sheet,
go to the VBE Immediate Window and type

? selection.value2

hit enter
if it give 45017 the date is still April 1, 2023

 

[ipbr21054]

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