Listbox to show dd/mm/yyyy & not mm/dd/yyyy

[ipbr21054]

I recently changed a value in the settings to show proper dates as opposed text dates.

Now after a fews days & using my other forms i see two which show dates like 9/24/2024 & so i need it to correctly show 24/09/2024

Below are the two Userform_Initialize codes in question but im unable to get the date shown correctly when usng the following code attempt.

ListBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")

FORM 1

Private Sub UserForm_Initialize()

Dim fndRng As Range
Dim firstAddress As String
Dim cnt As Long
Dim elapsedDays As Long

With Me.ListBox1
.ColumnCount = 9
.ColumnWidths = "220;170;110;170;120;30;130;0"
End With

With Sheets("POSTAGE").Range("G:G")
Set fndRng = .Find(What:="DELIVERED NO SIG", LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not fndRng Is Nothing Then
firstAddress = fndRng.Address
Do ' check the date
elapsedDays = Date - DateValue(fndRng.Offset(, -6))
If elapsedDays <= 80 And elapsedDays >= 30 Then

cnt = cnt + 1
With Me.ListBox1 ' ADD VALUES TO LISTBOX
.AddItem fndRng.Offset(, -5).Value 'CUSTOMER'S NAME
.List(.ListCount - 1, 1) = fndRng.Offset(, -4).Value 'ITEM
.List(.ListCount - 1, 2) = fndRng.Offset(, -6).Value 'DATE
.List(.ListCount - 1, 3) = fndRng.Offset(, -2).Value 'TRACKING NUMBER
.List(.ListCount - 1, 4) = fndRng.Offset(, 5).Value 'CLAIM
.List(.ListCount - 1, 6) = fndRng.Value 'RECEIVED NO SIG
.List(.ListCount - 1, 8) = fndRng.Row
End With
End If
Set fndRng = .FindNext(fndRng)
Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
End If
End With

If cnt = 0 Then
MsgBox "THERE ARE " & cnt & " RECORDS FOR WITHIN THE LAST 80 DAYS", vbInformation, "DELIVERED BUT NO SIGNATURE MESSAGE"
End
End If
Me.StartUpPosition = 0
Me.Top = Application.Top + 100 ' MARGIN FROM TOP OF SCREEN
Me.Left = Application.Left + Application.Width - Me.Width - 70 ' LEFT / RIGHT OF SCREEN
End Sub

FORM2

Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

On Error GoTo End_here

Call add_val("LOST")
Call add_val("DELIVERED NO SIG")
Call add_val("RETURNED")
Call add_val("UNKNOWN")

End_here: Application.ScreenUpdating = True
End Sub

 

[abdelfattah]

not really sure
maybe this choice works

.List(.ListCount - 1, 2) = Format(fndRng.Offset(, -6).Value, "dd/mm/yyyy")

or
make sure inside the sheet for DATE column "dd/mm/yyyy and use this

.List(.ListCount - 1, 2) =fndRng.Offset(, -6).Text

 

[ipbr21054]

That worked thanks.
I found one more so can you also advise for that one,i did try to add the code you show for it but didnt change ?

Private Function add_val(A As String)

      Dim r As Range, f As Range, Cell As String, added As Boolean
      Dim sh As Worksheet
      
      Set sh = Sheets("POSTAGE")
      sh.Select
      With ListBox1
        
        .ColumnCount = 5
        .ColumnWidths = "170;260;220;130;30"

        Set r = Range("A2183", Range("G" & Rows.count).End(xlUp)) ' THIS IS THE ROW NUMBER TO SEARCH DOWN FROM
        
        Set f = r.Find(A, 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                 'POSTAL ISSUE COLUMN
              .List(i, 1) = f.Offset(, -5).Value  'CUSTOMER
              .List(i, 2) = f.Offset(, -4).Value  'ITEM
              .List(i, 3) = f.Offset(, -6).Value  'DATE
              .List(i, 4) = f.Row                 'ROW
              added = True
              Exit For
          End Select

            Next
            If added = False Then
          .AddItem f.Value                                 'POSTAL ISSUE COLUMN
          .List(.ListCount - 1, 1) = f.Offset(, -5).Value  'CUSTOMER
          .List(.ListCount - 1, 2) = f.Offset(, -4).Value  'NAME
          .List(.ListCount - 1, 3) = f.Offset(, -6).Value  'DATE
          .List(.ListCount - 1, 4) = f.Row                 'ROW
        End If

            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> Cell
        Else
        End If
      End With
End Function

 

[abdelfattah]

I don't see any change in your code based on my suggestion !

 

[ipbr21054]

This is another so left it original so you could see,the other has your code applied & date now correct

I used this one.

.List(.ListCount - 1, 2) = Format(fndRng.Offset(, -6).Value, "dd/mm/yyyy")

 

[abdelfattah]

I suggest to attach file maybe you get more chance to help you by members.

I used this one.

what about second choice?

 

[jkpieterse]

An excellent resource about international issues like this one: Office Automation Ltd. - Excel 2002/2003 VBA Programmer's Reference 20 years old, but still very relevant today!

 

[ipbr21054]

I suggest to attach file maybe you get more chance to help you by members.

what about second choice?

Made no difference

 

[ipbr21054]

An excellent resource about international issues like this one: Office Automation Ltd. - Excel 2002/2003 VBA Programmer's Reference 20 years old, but still very relevant today!

Thanks but its not a regional setting issue

 

[ipbr21054]

@Alex Blakenburg maybe you can help / See the issue as yout advised in another opost formatting date issue.

Below is the code in use & as per screenshot you will see that date is mm dd yyy
I have tried the following lines of code but i was either given a error message or there was no change at all or the form opened but date was then replaced with a 0

ListBox1.Value = Format(Date, "dd/mm/yyyy")=
ListtBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
.List(.ListCount - 1, 2) = Format(fndRng.Offset(, -6).Value, "dd/mm/yyyy")
.List(.ListCount - 1, 2) =fndRng.Offset(, -6).Text

Private Function add_val(A As String)
    
      Dim r As Range, f As Range, Cell As String, added As Boolean
      Dim sh As Worksheet
      
      Set sh = Sheets("POSTAGE")
      sh.Select
      With ListBox1
        
        .ColumnCount = 5
        .ColumnWidths = "170;260;220;130;30"

        Set r = Range("A2183", Range("G" & Rows.count).End(xlUp)) ' THIS IS THE ROW NUMBER TO SEARCH DOWN FROM
        
        Set f = r.Find(A, 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                 'POSTAL ISSUE COLUMN
              .List(i, 1) = f.Offset(, -5).Value  'CUSTOMER
              .List(i, 2) = f.Offset(, -4).Value  'ITEM
              .List(i, 3) = f.Offset(, -6).Value  'DATE
              .List(i, 4) = f.Row                 'ROW
              added = True
              Exit For
          End Select

            Next
            If added = False Then
          .AddItem f.Value                                 'POSTAL ISSUE COLUMN
          .List(.ListCount - 1, 1) = f.Offset(, -5).Value  'CUSTOMER
          .List(.ListCount - 1, 2) = f.Offset(, -4).Value  'NAME
          .List(.ListCount - 1, 3) = f.Offset(, -6).Value  'DATE
          .List(.ListCount - 1, 4) = f.Row                 'ROW
        End If

            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> Cell
        Else
        End If
      End With
End Function